SQL Performance Analyzer实操
1、什么是SPA(SQL Performance Analyzer)？
SPA(
SQL Performance Analyzer)
是Oracle的SQL性能优化分析器。Oracle Database 11g 引入了 SQL 性能分析器；使用该工具可以准确地评估数据库变更对 SQL 语句的影响。SQL 性能分析器可帮助预测潜在的更改对 SQL 的性能影响。这种功能可向 DBA 提供有关 SQL 语句性能的详细信息，例如，执行前后的统计信息，提高或降低性能的语句。
SPA其实也是Oracle Real Application Testing的一个组件，另外一个组件Database Replay。SPA的原理是通过变更前对收集的STS进行执行，获取基线数据，变更后再次进行执行，然后进行对比，从多个维度比如CPU时间，I/O，buffer get等生成详细的变更对比报告。典型用法是捕捉STS，然后通过SPA生成对比报告，然后对变更后衰减的SQL调用STA进行优化。
2、SPA作用
SQL 性能分析器可用于预测和防止会影响 SQL 执行计划结构的任何数据库环境更改所带来的潜在性能问题。这些更改可以包括（但不限于）以下任何一种更改：
1）数据库升级
2）实施优化建议
3）更改方案
4）收集统计信息
5）更改数据库参数
6）更改操作系统和硬件
3、SPA测试流程
为了尽可能的减小对正式生产库的性能影响，SPA测试可以从AWR资料库中的SQL数据转化而来的SQL Tuning Set进行整体的SQL性能测试。
本次测试主要分为以下几个步骤：
在源端：
1.环境准备：创建SPA测试专用用户
2.采集数据：
a)在生产库转化AWR中SQL为SQL Tuning Set
b)在生产库从现有SQL Tuning Set提取SQL
3.导出数据：打包(pack)转化后的SQL Tuning Set，并导出传输到测试
服务器
在目标库端：
1.环境准备：创建SPA测试专用用户
2.测试准备：导入SQL Tuning Set表，并解包(unpack)，创建SPA分析任务
3.前期性能：从SQL Tuning Set中转化得出11g的性能Trail
4.后期性能：在19c测试数据库中执行SQL Tuning Set中SQL，生成19c性能Trail
5.对比分析：执行对比分析任务，分别按执行时间，CPU时间和逻辑读三个维度进行
6.汇总报告：取出对比报告，对每个维度分别取出All，Unsupport，Error 3类报告
总结报告：分析汇总报告，优化其中的性能下降SQL，编写SPA测试报告
4、SPA实操
4.1、初始化数据库（源端和目标端数据库）
在进行SPA操作前，需要为数据库进行检测，目标端的数据库的表空间的大小和名字需要和源端的表空间的大小和名字一致（除去系统表空间）
a、检测源端数据库的表空间
SQL> select tablespace_name,bytes/1024/1024m from dba_data_files;
TABLESPACE_NAME M
------------------------------ ----------
SYSTEM 700
SYSAUX 600
UNDOTBS1 200
USERS 5
TEST 100
b、查看目标端表空间
SQL> select tablespace_name,bytes/1024/1024m from dba_data_files;
TABLESPACE_NAME M
------------------------------ ----------
SYSTEM 900
SYSAUX 600
USERS 5
UNDOTBS1 55
通过对表空间的检测可以看到，目标端没有test表空间，需要在目标端创建一个名为TEST，大小为100 MB的表空间
c、目标端进行表空间创建
查看表空间的位置
SQL> col file_name for a80
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_SYSTEM_GJ3K66GF_.DBF SYSTEM
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_SYSAUX_GJ3K79OK_.DBF SYSAUX
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_USERS_GJ3K7T1V_.DBF USERS
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_UNDOTBS1_GJ3K7RWQ_.DBF UNDOTBS1
创建表空间
SQL> create tablespace test datafile 'C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\TEST.DBF' size 100m;
表空间已创建。
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_SYSTEM_GJ3K66GF_.DBF SYSTEM
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_SYSAUX_GJ3K79OK_.DBF SYSAUX
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\TEST.DBF TEST
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_USERS_GJ3K7T1V_.DBF USERS
C:\ORACLE\APP\LI\ORADATA\HAPPY\LUFFY\DATAFILE\O1_MF_UNDOTBS1_GJ3K7RWQ_.DBF UNDOTBS1
4.2、导入导出数据
把源端的tns拷贝到目标端（测试不需要，如果是生产库则需要进行tns的拷贝）
同时需要注意DB link
a、源端导出数据
查看directory
SQL> col DIRECTORY_PATH for a80
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS ORACLE_OCM_CONFIG_DIR /oracle/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/source/state
SYS DATA_PUMP_DIR /oracle/app/oracle/admin/source/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 /oracle/app/oracle/product/11.2.0/dbhome_1/ccr/state
创建directory
SQL> create directory dump_dir as '/oracle/app/dump';
Directory created.
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS DUMP_DIR /oracle/app/dump
SYS ORACLE_OCM_CONFIG_DIR /oracle/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/source/state
SYS DATA_PUMP_DIR /oracle/app/oracle/admin/source/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 /oracle/app/oracle/product/11.2.0/dbhome_1/ccr/state
源端导出数据
[oracle@source dump]$ cat /oracle/app/dump/full.sh
PATH=$PATH:$HOME/bin
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK; export NLS_LANG
export PATHORACLE_SID=source; export ORACLE_SID
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
export LANG=C
export 022
/oracle/app/oracle/product/11.2.0/dbhome_1/bin/expdp system/oracle DIRECTORY=dump_dir parallel=1 dumpfile=source_full_` date +%y%m%d`_%U.dmp logfile=source_full_` date +%y%m%d`.log compression=all cluster=N full=y
目标端导入数据
C:\Users\li>impdp ' / as sysdba' directory=dump parallel=1 dumpfile=source_full_190617_01.dmp logfile=impdp.source.full.log cluster=no full=y
Import: Release 19.0.0.0.0 - Production on 星期二 6月 18 14:40:49 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
口令:oracle
;;;
Import: Release 19.0.0.0.0 - Production on 星期二 6月 18 14:40:49 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
;;;
连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_FULL_01"
启动 "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=dump parallel=1 dumpfile=source_full_190617_01.dmp logfile=impdp.source.full.log cluster=no full=y
处理对象类型 DATABASE_EXPORT/TABLESPACE
导入报错内容
（1）表空间、角色、用户、序列等已存在，忽略
ORA-31684: 对象类型 TABLESPACE:"UNDOTBS1" 已存在
ORA-31684: 对象类型 TABLESPACE:"TEMP" 已存在
ORA-31684: 对象类型 TABLESPACE:"USERS" 已存在
ORA-31684: 对象类型 TABLESPACE:"TEST" 已存在
ORA-31685: 由于权限不足, 对象类型 USER:"SYS" 失败。失败的 sql 为:
ALTER USER "SYS" IDENTIFIED BY VALUES 'S:6BF11CCC7A4D3D308B5CF151AE6BE8E8981F1612723B5B95DDDD17182B38;8A8F025737A9097A' TEMPORARY TABLESPACE "TEMP"
ORA-31684: 对象类型 USER:"OUTLN" 已存在
ORA-31684: 对象类型 USER:"ORACLE" 已存在
ORA-31684: 对象类型 ROLE:"SELECT_CATALOG_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"EXECUTE_CATALOG_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"DBFS_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"AQ_ADMINISTRATOR_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"AQ_USER_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"ADM_PARALLEL_EXECUTE_TASK" 已存在
ORA-31684: 对象类型 ROLE:"GATHER_SYSTEM_STATISTICS" 已存在
ORA-31684: 对象类型 ROLE:"RECOVERY_CATALOG_OWNER" 已存在
ORA-31684: 对象类型 ROLE:"SCHEDULER_ADMIN" 已存在
ORA-31684: 对象类型 ROLE:"HS_ADMIN_SELECT_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"HS_ADMIN_EXECUTE_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"HS_ADMIN_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"GLOBAL_AQ_USER_ROLE" 已存在
ORA-31684: 对象类型 ROLE:"OEM_ADVISOR" 已存在
ORA-31684: 对象类型 ROLE:"OEM_MONITOR" 已存在
ORA-31684: 对象类型 ROLE:"WM_ADMIN_ROLE" 已存在
ORA-31684: 对象类型 DIRECTORY:"DATA_PUMP_DIR" 已存在
ORA-31684: 对象类型 CONTEXT:"GLOBAL_AQCLNTDB_CTX" 已存在
ORA-31684: 对象类型 CONTEXT:"DBFS_CONTEXT" 已存在
ORA-31684: 对象类型 CONTEXT:"REGISTRY$CTX" 已存在
ORA-31684: 对象类型 CONTEXT:"LT_CTX" 已存在
ORA-39083: 对象类型 PROCACT_SYSTEM 创建失败, 出现错误:
ORA-20000: Incompatible version of Workspace Manager Installed
失败的 sql 为:
BEGIN
declare ver varchar2(100) ; cnt integer ; dummy integer; vdummy varchar2(30) ; compile_exception EXCEPTION; PRAGMA EXCEPTION_INIT(compile_exception, -06550); invalid_table EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_table, -00942); procedure createErrorProc is begin execute immediate 'create or replace function system.wm$_check_install return boolean is begin return true ; end;' ; end ; begin select status into vdummy from dba_registry where comp_id = 'OWM' ; if (vdummy not in ('VALID', 'UPGRADED', 'LOADED')) then raise compile_exception ; end if ; select 1 into dummy from dual where exists (select 1 from all_users where username = 'WMSYS') ; execute immediate 'create or replace function wmsys.wm$_old_version return varchar2 is begin return ''11.2.0.4.0'' ; end;' ; select count(*) into cnt from dba_procedures where object_name='LT_EXPORT_PKG' and procedure_name='IMPORTEXPORTSUPPORT'; if (cnt=0) then raise_application_error(-20000, 'Incompatible version of Workspace Manager Installed'); end if ; execute immediate 'select value from wmsys.wm$env_vars where name=''OWM_VERSION''' into ver ; if (0>0 and ver != '11.2.0.4.0') then raise_application_error(-20000, 'Unable to import due to one or more versioned tables not having a ''VERSIONED'' status'); end if ; execute immediate 'declare cnt integer ; begin select count(*) into cnt from (select workspace from wmsys.wm$version_hierarchy_table where version!=0 union all select table_name from wmsys.wm$versioned_tables) ; if (cnt>0)
ORA-39083: 对象类型 PROCACT_SYSTEM 创建失败, 出现错误:
ORA-20000: Incompatible version of Workspace Manager Installed
失败的 sql 为:
BEGIN
declare ver varchar2(100) ; cnt integer ; dummy integer; vdummy varchar2(30) ; compile_exception EXCEPTION; PRAGMA EXCEPTION_INIT(compile_exception, -06550); invalid_table EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_table, -00942); procedure createErrorProc is begin execute immediate 'create or replace function system.wm$_check_install return boolean is begin return true ; end;' ; end ; begin select status into vdummy from dba_registry where comp_id = 'OWM' ; if (vdummy not in ('VALID', 'UPGRADED', 'LOADED')) then raise compile_exception ; end if ; select 1 into dummy from dual where exists (select 1 from all_users where username = 'WMSYS') ; execute immediate 'create or replace function wmsys.wm$_old_version return varchar2 is begin return ''11.2.0.4.0'' ; end;' ; select count(*) into cnt from dba_procedures where object_name='LT_EXPORT_PKG' and procedure_name='IMPORTEXPORTSUPPORT'; if (cnt=0) then raise_application_error(-20000, 'Incompatible version of Workspace Manager Installed'); end if ; execute immediate 'select value from wmsys.wm$env_vars where name=''OWM_VERSION''' into ver ; if (0>0 and ver != '11.2.0.4.0') then raise_application_error(-20000, 'Unable to import due to one or more versioned tables not having a ''VERSIONED'' status'); end if ; execute immediate 'declare cnt integer ; begin select count(*) into cnt from (select workspace from wmsys.wm$version_hierarchy_table where version!=0 union all select table_name from wmsys.wm$versioned_tables) ; if (cnt>0)
ORA-39126: 在 KUPW$WORKER.SEND_TRACE_MSG [KUPW: ORA-39083: 对象类型 PROCACT_SYSTEM 创建失败, 出现错误:
ORA-20000: Incompatible version of Workspace Manager Installed
失败的 sql 为:
BEGIN
declare ver varchar2(100) ; cnt integer ; dummy integer; vdummy varchar2(30) ; compile_exception EXCEPTION; PRAGMA EXCEPTION_INIT(compile_exception, -06550); invalid_table EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_table, -00942); procedure createErrorProc is begin execute immediate 'create or replace function system.wm$_check_install return boolean is begin return true ; end;' ; end ; begin select status into vdummy from dba_registry where comp_id = 'OWM' ; if (vdummy not in ('VALID', 'UPGRADED', 'LOADED')) then raise compile_exception ; end if ; select 1 into dummy from dual where exists (select 1 from all_users where username = 'WMSYS') ; execute immediate 'create or replace function wmsys.wm$_old_version return varchar2 is begin return ''11.2.0.4.0'' ; end;' ; select count(*) into cnt from dba_procedures where object_name='LT_EXPORT_PKG' and procedure_name='IMPORTEXPORTSUPPORT'; if (cnt=0) then raise_application_error(-20000, 'Incompatible version of Workspace Manager Installed'); end if ; execute immediate 'select value from wmsys.wm$env_vars where name=''OWM_VERSION''' into ver ; if (0>0 and ver != '11.2.0.4.0') then raise_application_error(-20000, 'Unable to import due to one or more versioned tables not having a ''VERSIONED'' status'); end if ; execute immediate 'declare cnt integer ; begin select count(*) into cnt from (select workspace from wmsys.wm$version_hierarchy_table where version!=0 union all select table_name from wmsys.wm$versioned_tables) ; if (cnt>0)
] 中 Worker 发生意外致命错误
PROCACT_SYSTEM
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: 在 "SYS.KUPW$WORKER", line 12620
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: 在 "SYS.KUPW$WORKER", line 34278
----- PL/SQL Call Stack -----
object line object
handle number name
00007FF70BE8F840 33476 package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION
00007FF70BE8F840 12641 package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR
00007FF70BE8F840 34341 package body SYS.KUPW$WORKER.SEND_TRACE_MSG
00007FF70BE8F840 28767 package body SYS.KUPW$WORKER.SEND_MSG
00007FF70BE8F840 5418 package body SYS.KUPW$WORKER.LOAD_METADATA
00007FF70BE8F840 13781 package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS
00007FF70BE8F840 2429 package body SYS.KUPW$WORKER.MAIN
00007FF70BD34800 2 anonymous block
DBMS_LOB.TRIM
DBMS_LOB.FREETEMPORARY
DBMS_LOB.FREETEMPORARY
KUPW: In procedure UPDATE_TYPE_COMPLETION_ROW
KUPW: Old Seqno: 108 New Path: PO Num: -6 New Seqno: 0 error count: 1
KUPW: Primary row is: FALSE
KUPW: Working on old seqno with count of: 4
KUPW: In procedure SEND_MSG. Fatal=0
KUPW: Error count: 1
KUPW: In procedure DETERMINE_FATAL_ERROR with ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-39083: 对象类型 PROCACT_SYSTEM 创建失败, 出现错误:
ORA-29371: 暂挂区未激活
失败的 sql 为:
BEGIN
dbms_resource_manager.set_consumer_group_mapping_pri(1,7,6,9,8,10,5,4,3,2,11);
dbms_resource_manager.set_consumer_group_mapping('ORACLE_USER','"SYSTEM"','SYS_GROUP');
dbms_resource_manager.set_consumer_group_mapping('ORACLE_USER','"SYS"','SYS_GROUP');
dbms_resource_manager.set_consumer_group_mapping('ORACLE_FUNCTION','"BACKUP"','BATCH_GROUP');
dbms_resource_manager.set_consumer_group_mapping('ORACLE_FUNCTION','"COPY"','BATCH_GROUP');
dbms_resource_manager.set_consumer_group_mapping('ORACLE_FUNCTION','"DATALOAD"','ETL_GROUP');
dbms_resource_manager.submit_pending_area;COMMIT; END;
ORA-39083: 对象类型 PROCACT_SYSTEM 创建失败, 出现错误:
ORA-04042: 过程, 函数, 程序包或程序包体不存在
失败的 sql 为:
BEGIN
SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT EXECUTE ON DBMS_DEFER_SYS TO "DBA"');COMMIT; END;
4.3、源端与目标端进行SPA
4.3.1、源端操作
1、环境准备
创建SPA专用用户
create user spa identified by spa default tablespace users;
grant dba to spa;
grant advisor to spa;
grant select any dictionary to spa;
grant administer sql tuning set to spa;
2、采集数据
a)在生产库转化AWR中SQL为SQL Tuning Set
b)在生产库从现有SQL Tuning Set提取SQL
在生产端，使用Oracle SQL Tuning工具包，从AWR资料库数据中转化得到SQL Tuning Set，用于整个SPA测试流程中的SQL来源。
为了确保对生产环境影响最小，我们只对生产端采集AWR的SQL，具体采集步骤如下：
a、获取AWR快照的边界ID
set lines 188 pages 1000
col snap_time for a22
col min_id new_value minid
col max_id new_value maxid
select min(snap_id) min_id, max(snap_id) max_id
from dba_hist_snapshot
where end_interval_time > trunc(sysdate)-30
order by 1;
MIN_ID MAX_ID
---------- ----------
20 20
b、
创建SQL Set
连接用户：
conn spa/spa
如果之前有这个SQLSET的名字，可以这样删除：
EXEC DBMS_SQLTUNE.DROP_SQLSET (SQLSET_NAME => 'SOL_SQLSET_201906', SQLSET_OWNER => 'SPA');
新建SQLSET：SOL_SQLSET_201906
EXEC DBMS_SQLTUNE.CREATE_SQLSET (-
SQLSET_NAME => 'SOL_SQLSET_201906',-
DESCRIPTION => 'SQL Set Create at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),-
SQLSET_OWNER => 'SPA');
查询sql set信息：
col DESCRIPTION for a50
select owner,name,STATEMENT_COUNT,DESCRIPTION,CREATED from dba_sqlset;
OWNER NAME STATEMENT_COUNT DESCRIPTION CREATED
------------------------------ ------------------------------ --------------- -------------------------------------------------- ---------
SPA SOL_SQLSET_201906 0 SQL Set Create at : 2019-06-17 23:57:05 17-JUN-19
c、
转化AWR数据中的SQL数据，将其中的SQL载入到SQL Set中
注意：过滤太多的账户会报错
从AWR中提取：
DECLARE
SQLSET_CUR DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN SQLSET_CUR FOR
SELECT VALUE(P) FROM TABLE(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( 20, 21,
'PARSING_SCHEMA_NAME NOT IN (''SYS'', ''SYSTEM'')',
NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P;
DBMS_SQLTUNE.LOAD_SQLSET(
SQLSET_NAME => 'SOL_SQLSET_201906',
SQLSET_OWNER => 'SPA',
POPULATE_CURSOR => SQLSET_CUR,
LOAD_OPTION => 'MERGE',
UPDATE_OPTION => 'ACCUMULATE');
CLOSE SQLSET_CUR;
END;
/
查询sql set信息：
col DESCRIPTION for a50
select owner,name,STATEMENT_COUNT,DESCRIPTION,CREATED from dba_sqlset;
OWNER NAME STATEMENT_COUNT DESCRIPTION CREATED
------------------------------ ------------------------------ --------------- -------------------------------------------------- ---------
SPA SOL_SQLSET_201906 5 SQL Set Create at : 2019-06-17 23:57:05 17-JUN-19
d、
转化当前cursor cache中的SQL数据，将其中的SQL载入到SQL Set中
从当前cursor cache中提取：排除sys、system用户执行的语句
DECLARE
SQLSET_CUR DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN SQLSET_CUR FOR
SELECT VALUE(P) FROM TABLE(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'PARSING_SCHEMA_NAME NOT IN (''SYS'', ''SYSTEM'')',
NULL, NULL, NULL, NULL, 1, NULL,'ALL')) P;
DBMS_SQLTUNE.LOAD_SQLSET(
SQLSET_NAME => 'SOL_SQLSET_201906',
SQLSET_OWNER => 'SPA',
POPULATE_CURSOR => SQLSET_CUR,
LOAD_OPTION => 'MERGE',
UPDATE_OPTION => 'ACCUMULATE');
CLOSE SQLSET_CUR;
END;
/
查询sql set信息：
col DESCRIPTION for a50
select owner,name,STATEMENT_COUNT,DESCRIPTION,CREATED from dba_sqlset;
OWNER NAME STATEMENT_COUNT DESCRIPTION CREATED
------------------------------ ------------------------------ --------------- -------------------------------------------------- ---------
SPA SOL_SQLSET_201906 36 SQL Set Create at : 2019-06-17 23:57:05 17-JUN-19
e、
打包SQL Set
DROP TABLE SPA.SOL_STSTAB_201906;
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET ('SOL_STSTAB_201906', 'SPA', 'USERS');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET ( -
SQLSET_NAME => 'SOL_SQLSET_201906', -
SQLSET_OWNER => 'SPA', -
STAGING_TABLE_NAME => 'SOL_STSTAB_201906', -
STAGING_SCHEMA_OWNER => 'SPA');
查看spa下用户下的表对象：
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SOL_STSTAB_201906 TABLE
4.3.3、源端操作（导出SPA的数据）
打包(pack)转化后的SQL Tuning Set，并导出传输到测试服务器
将采集到的数据打包后，需要将其中生产库导出，并传输到测试服务器中，用于在测试数据库中进行SPA测试工作。
1）在操作系统中，导出打包后的SQL Set数据
[oracle@source ~]$ cat /home/oracle/export_sqlset_201903.par
USERID=spa/spa
FILE=SOL_STSTAB_201906.dmp
LOG=exp_spa_sqlset_201906.log
TABLES=SOL_STSTAB_201906
DIRECT=Y
BUFFER=10240000
STATISTICS=NONE
导出数据
[oracle@source ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@source ~]$ exp PARFILE=export_sqlset_201906.par
Export: Release 11.2.0.4.0 - Production on Tue Jun 18 00:17:57 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...
Table SOL_STSTAB_201906 will be exported in conventional path.
. . exporting table SOL_STSTAB_201906 183 rows exported
Export terminated successfully without warnings.
2）将导出后的Dump文件传输到测试服务器
将SOL_STSTAB_201906.dmp 传输到 目标服务器
[C:\Users\li]
下：
4.3.4、目标端操作
1、环境准备
创建SPA专用用户
为了进行SPA测试，在测试数据库中创建SPA测试专用用户，避免与其他用户相互混淆与可能产生的误操作。
create user spa identified by spa default tablespace users;
grant dba to spa;
grant advisor to spa;
grant select any dictionary to spa;
grant administer sql tuning set to spa;
2、
测试准备
导入SQL Tuning Set表，并解包(unpack)，创建SPA分析任务
在进行SPA测试前需要准备测试环境，包括导入生产库中的SQL Set，对其进行解包(unpack)操作，并创建SPA分析任务。
1）在操作系统中，执行导入命令，导入SQL Set表
###win使用imp，进入到dmp的文件位置
C:\Users\li>imp USERID=spa/spa FILE=SOL_STSTAB_201906.dmp LOG=imp_spa_sqlset_201906.log FULL=Y
Import: Release 19.0.0.0.0 - Production on 星期二 6月 18 15:33:34 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
经由直接路径由 EXPORT:V11.02.00 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
IMP-00403:
警告: 此导入生成了单独的 SQL 文件 "imp_spa_sqlset_201906_sys.sql", 其中包含了由于权限问题而失败的 DDL。
. 正在将 SPA 的对象导入到 SPA
. 正在将 SPA 的对象导入到 SPA
IMP-00015: 由于对象已存在, 下列语句失败:
"CREATE PUBLIC SYNONYM "ANYDATA" FOR "SYS"."ANYDATA""
. . 正在导入表 "SOL_STSTAB_201906"导入了 183 行
成功终止导入, 但出现警告。
2）连接到spa
C:\Users\li>sqlplus spa/spa
3）解包(unpack)SQL Set
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (-
SQLSET_NAME => 'SOL_SQLSET_201906', -
SQLSET_OWNER => 'SPA', -
REPLACE => TRUE, -
STAGING_TABLE_NAME => 'SOL_STSTAB_201906', -
STAGING_SCHEMA_OWNER => 'SPA');
4）创建SPA分析任务
VARIABLE SPA_TASK VARCHAR2(64);
EXEC :SPA_TASK := DBMS_SQLPA.CREATE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_201906', -
DESCRIPTION => 'SPA Analysis task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), -
SQLSET_NAME => 'SOL_SQLSET_201906', -
SQLSET_OWNER => 'SPA');
建议设置设置超过时间，超过1800秒还未执行完成就跳过处理：
exec dbms_sqlpa.set_analysis_default_parameter('LOCAL_TIME_LIMIT',1800);
select parameter_value from dba_advisor_def_parameters where advisor_name='SQL Performance Analyzer' and parameter_name='LOCAL_TIME_LIMIT';
PARAMETER_VALUE
--------------------------------------------------------------------------------
1800
exec dbms_sqlpa.set_analysis_default_parameter('BASIC_FILTER','1akvyr72fsyyj');
有些sql可能存在问题，可以把这些条sql从基表WRI$_SQLSET_STATEMENTS中删除或者在创建分析任务时加上过滤：
VARIABLE SPA_TASK VARCHAR2(64);
EXEC :SPA_TASK := DBMS_SQLPA.CREATE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_201903', -
BASIC_FILTER=>'sql_id not in(''1akvyr72fsyyj'',''cjjb62udz141p'')',-
DESCRIPTION => 'SPA Analysis task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), -
SQLSET_NAME => 'SOL_SQLSET_201903', -
SQLSET_OWNER => 'SPA');
为了删除掉有问题的SQL
查看分析任务信息：
set linesize 400 pagesize 9999
col owner for a20
col task_name for a20
alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';
select owner,task_id,task_name,created,last_modified,status from dba_advisor_tasks where task_name='SPA_TASK_201906' order by 2;
OWNER TASK_ID TASK_NAME CREATED LAST_MODIFIED STATUS
-------------------- ---------- -------------------- ------------------- ------------------- -----------
SPA 62 SPA_TASK_201906 2019-06-18 15:38:41 2019-06-18 15:38:42 INITIAL
3、前期性能
从SQL Tuning Set中转化得出11g的性能Trail
EXECUTION_TYPE参数介绍：
Type of the action to perform by the function. If NULL it will default to the value of the DEFAULT_EXECUTION_TYPE parameter. Possible values are:
1）[TEST] EXECUTE – test-execute every SQL statement and collect its execution plans and execution statistics. The resulting plans and statistics will be stored in the advisor framework. This is default.
2）EXPLAIN PLAN – generate explain plan for every statement in the SQL workload. This is similar to the EXPLAIN PLAN command. The resulting plans will be stored in the advisor framework in association with the task.
3）COMPARE [PERFORMANCE] – analyze and compare two versions of SQL performance data. The performance data is generated by test-executing or generating explain plan of the SQL statements. Use this option when two executions of type EXPLAIN_PLAN or TEST_EXECUTE already exist in the task
4）CONVERT SQLSET – used to read the statistics captured in a SQL Tuning Set and model them as a task execution. This can be used when you wish to avoid executing the SQL statements because valid data for the experiment already exists in the SQL Tuning Set.
在测试服务器中，可以直接从SQL Tuning Set中转化得到所有SQL在11g数据库中的执行效率，得到11g中的SQL Trail。
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_201906', -
EXECUTION_NAME => 'EXEC_11G_201906', -
EXECUTION_TYPE => 'CONVERT SQLSET', -
EXECUTION_DESC => 'Convert 11g SQLSET for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
4、后期性能
在19c测试数据库中执行SQL Tuning Set中SQL，生成19c性能Trail
在测试服务器（运行19据库）中，需要在本地数据库（19c）测试运行SQL Tuning Set中的SQL语句，分析所有语句在19c环境中的执行效率，得到19c中的SQL Trail。
脚本内容如下：
vi /home/oracle/spa2.sh
echo "WARNING: SPA2 Start @`date`"
sqlplus spa/spa << EOF!
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_201906', -
EXECUTION_NAME => 'EXEC_19C_201906', -
EXECUTION_TYPE => 'TEST EXECUTE', -
EXECUTION_DESC => 'Execute SQL in 19c for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
exit
EOF!
echo "WARNING:SPA2 OK @`date`"
脚本赋予执行权限：
chmod +x spa2.sh
后台执行脚本：
nohup sh spa2.sh &
脚本执行日志如下：
cat nohup.out
可以通过如下方式暂停、恢复、删除分析任务，过滤执行sql：
conn spa/spa
exec dbms_sqlpa.interrupt_analysis_task('SPA_TASK_201906');
中断的任务可以恢复：
exec dbms_sqlpa.resume_analysis_task('SPA_TASK_201906');
删除分析任务：
exec dbms_sqlpa.drop_analysis_task('SPA_TASK_201906');
此次win直接使用命令
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_201906', -
EXECUTION_NAME => 'EXEC_19C_201906', -
EXECUTION_TYPE => 'TEST EXECUTE', -
EXECUTION_DESC => 'Execute SQL in 19c for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
5、对比报告
执行对比分析任务，分别按执行时间，CPU时间和逻辑读三个维度进行
得到两次SQL Trail之后，可以对比两次Trial之间的SQL执行性能，可以从不同的维度对两次Trail中的所有SQL进行对比分析，主要关注的维度有：SQL执行时间，SQL执行的CPU时间，SQL执行的逻辑读。
注意：在spa用户下执行
1）对比两次Trail中的SQL执行时间
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_201906', -
EXECUTION_NAME => 'COMPARE_ET_201906', -
EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
'COMPARISON_METRIC', 'ELAPSED_TIME', -
'EXECUTE_FULLDML', 'TRUE', -
'EXECUTION_NAME1','EXEC_11G_201906', -
'EXECUTION_NAME2','EXEC_19C_201906'), -
EXECUTION_DESC => 'Compare SQLs between 11g and 19c at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
说明：默认情况下SPA若涉及到DML语句则只有查询部分Query会被执行，但是从11.2开始可以执行完全的DML了，需要加入参数EXECUTE_FULLDML，但是该参数目前有一些BUG:
Bug 10428438 : WITH EXECUTE_FULLDML ROWS IS ALWAYS SET TO 0 11.2.0.1
Bug 14635522 : SPA SHOULD CAPTURE AND REPLAY TRANSACTIONS 11.2.0.3
By default, only the query portion of DMLs is executed. Using APIs, you can execute the full DML by using the EXECUTE_FULLDML task parameter.EXECUTE_FULLDML when set to TRUE executes DML statement fully, including acquiring row locks and modifying rows; When EXECUTE_FULLDML is set to FALSE (the default value is false) to execute only the query part of the DML without modifying data. When TRUE, SQL Performance Analyzer will issue a rollback following DML execution to prevent persistent changes from being made by the DML. So SPA does not make make any change to the data in the tables.
2）对比两次Trail中的SQL执行的CPU时间
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_201906', -
EXECUTION_NAME => 'COMPARE_CT_201906', -
EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
'COMPARISON_METRIC', 'CPU_TIME', -
'EXECUTION_NAME1','EXEC_11G_201906', -
'EXECUTION_NAME2','EXEC_19C_201906'), -
EXECUTION_DESC => 'Compare SQLs between 11g and 19c at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
3）对比两次Trail中的SQL执行的逻辑读
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_201906', -
EXECUTION_NAME => 'COMPARE_BG_201906', -
EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
'COMPARISON_METRIC', 'BUFFER_GETS', -
'EXECUTION_NAME1','EXEC_11G_201906', -
'EXECUTION_NAME2','EXEC_19C_201906'), -
EXECUTION_DESC => 'Compare SQLs between 11g and 19c at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
6、汇总报告
取出对比报告，对每个维度分别取出All，Unsupport，Error 3类报告
执行对比分析任务之后，就可以取出对应的对比分析任务的结果报告，主要关注的报告类型有：汇总SQL报告，错误SQL报告以及不支持SQL报告。
1）获取执行时间全部报告（已完成，耗时11小时）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL elapsed_all.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201906','HTML','ALL','ALL',NULL,3000,'COMPARE_ET_201906')).GETCLOBVAL(0,0) FROM DUAL;
spool off
2）获取执行时间下降报告（已完成）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL elapsed_regressed.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201903','HTML','REGRESSED','ALL',NULL,NULL,'COMPARE_ET_201903')).GETCLOBVAL(0,0) FROM DUAL;
spool off
3）获取逻辑读全部报告（已完成）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL buffer_all.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201906','HTML','ALL','ALL',NULL,3000,'COMPARE_BG_201906')).GETCLOBVAL(0,0) FROM DUAL;
spool off
4）获取逻辑读下降报告（已完成）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL buffer_regressed.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201906','HTML','REGRESSED','ALL',NULL,NULL,'COMPARE_BG_201906')).GETCLOBVAL(0,0) FROM DUAL;
spool off
5）获取错误报告（已完成）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL error.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201906','HTML','ERRORS','ALL',NULL,NULL,'COMPARE_ET_201906')).GETCLOBVAL(0,0) FROM DUAL;
spool off
6）获取不支持报告（已完成）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL unsupported.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201906','HTML','UNSUPPORTED','ALL',NULL,NULL,'COMPARE_ET_201906')).GETCLOBVAL(0,0) FROM DUAL;
spool off
7）获取执行计划变化报告（已完成，耗时12小时）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL changed_plans.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201906','HTML','CHANGED_PLANS','ALL',NULL,3000,'COMPARE_ET_201906')).GETCLOBVAL(0,0) FROM DUAL;
spool off
8）获取执行超时报告（已完成）
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL timeout.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_201906','HTML','TIMEOUT','ALL',NULL,NULL,'COMPARE_ET_201906')).GETCLOBVAL(0,0) FROM DUAL;
spool off
成此最终的SPA性能分析报告
最终完成了linux到win的11g至19c的性能SPA报告
