Oracle SPA（SQL Performance Analyzer）實操

李佳豪2020-02-15

2104

本次SPA實操主要是基于redhat6.4+11.2.0.4的源端，目标端為win+19.3.0.0（11g到19c的SPA操作）

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 執行計劃結構的任何資料庫環境更改所帶來的潛在性能問題。這些更改可以包括（但不限于）以下任何一種更改：

資料庫更新
實施優化建議
更改方案
收集統計資訊
更改資料庫參數
更改作業系統和硬體

3、SPA測試流程

為了盡可能的減小對正式生産庫的性能影響，SPA測試可以從AWR資料庫中的SQL資料轉化而來的SQL Tuning Set進行整體的SQL性能測試。

本次測試主要分為以下幾個步驟：

在源端：

環境準備：建立SPA測試專用使用者
采集資料：

a)在生産庫轉化AWR中SQL為SQL Tuning Set

b)在生産庫從現有SQL Tuning Set提取SQL
導出資料：打包(pack)轉化後的SQL Tuning Set，并導出傳輸到測試伺服器

在目标庫端：

測試準備：導入SQL Tuning Set表，并解包(unpack)，建立SPA分析任務
前期性能：從SQL Tuning Set中轉化得出11g的性能Trail
後期性能：在19c測試資料庫中執行SQL Tuning Set中SQL，生成19c性能Trail
對比分析：執行對比分析任務，分别按執行時間，CPU時間和邏輯讀三個次元進行
彙總報告：取出對比報告，對每個次元分别取出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> 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、源端導出資料

[oracle@source dump]$ ./full.sh 
/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

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、采集資料

在生産端，使用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;
/

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

2）将導出後的Dump檔案傳輸到測試伺服器

将SOL_STSTAB_201906.dmp 傳輸到目标伺服器

4.3.4、目标端操作

為了進行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;

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表

C:\Users\li>imp USERID=spa/spa FILE=SOL_STSTAB_201906.dmp LOG=imp_spa_sqlset_201906.log FULL=Y

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

檢視分析任務資訊：

set linesize 400 pagesize 9999
col owner for a20
col task_name for a20
alter session set nls_date_format='yyyy-mm-dd hh24: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

在測試伺服器中，可以直接從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。

 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'));

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）擷取執行時間全部報告

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）擷取執行計劃變化報告

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性能分析報告

sql優化oracle