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中提?。号懦齭ys、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報告
