天天看點
傳回

記錄一則完整的SPA(10g->11g)測試過程1.SPA測試流程2.SPA操作流程3.SPA環境清理

生産端：Windows 2008 + Oracle 10.2.0.5

測試端：RHEL 6.5 + Oracle 11.2.0.4

需求：因為Oracle跨越大版本，優化器、新特性變動較多，需要進行SPA測試比對前後期性能差異。

說明：本文是根據DBA Travel的SPA參考規範文檔（在此緻謝Travel同學），結合實際某客戶需求整理的整個測試過程。為了更真實的反映整個過程，在生産端使用swingbench壓力測試軟體持續運作了一段時間，模拟真實的業務壓力。

  • 1.SPA測試流程
  • 2.SPA操作流程
  • 3.SPA環境清理

1.SPA測試流程

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

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

在生産庫端：

  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中轉化得出10g的性能Trail
  4. 後期性能：在11g測試資料庫中執行SQL Tuning Set中SQL，生成11g性能Trail
  5. 對比分析：執行對比分析任務，分别按執行時間，CPU時間和邏輯讀三個次元進行
  6. 彙總報告：取出對比報告，對每個次元分别取出All，Unsupport，Error 3類報告

總結報告：

  1. 總結報告：分析彙總報告，優化其中的性能下降SQL，編寫SPA測試報告

2.SPA操作流程

2.1 本文使用的命名規劃

類型                 規劃
SQLSET             ORCL_SQLSET_201806
Analysis Task        SPA_TASK_201806
STGTAB             ORCL_STSTAB_201806
Dmpfile            ORCL_STSTAB_201806.dmp

複制

2.2 生産端：環境準備

conn / as sysdba
CREATE USER SPA IDENTIFIED BY SPA DEFAULT TABLESPACE SYSAUX;
GRANT DBA TO SPA;
GRANT ADVISOR TO SPA;
GRANT SELECT ANY DICTIONARY TO SPA;
GRANT ADMINISTER SQL TUNING SET TO SPA;

複制

2.3 生産端：采集資料

1). 擷取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)-10
 ORDER BY 1;

複制

2). 建立SQL Set

--連接配接使用者
conn SPA/SPA

--如果之前有這個SQLSET的名字，可以這樣删除
EXEC DBMS_SQLTUNE.DROP_SQLSET (SQLSET_NAME  => 'ORCL_SQLSET_201806', SQLSET_OWNER => 'SPA');

--建立SQLSET：ORCL_SQLSET_201806
EXEC DBMS_SQLTUNE.CREATE_SQLSET ( -
                  SQLSET_NAME  => 'ORCL_SQLSET_201806', -
                  DESCRIPTION  => 'SQL Set Create at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), -
                  SQLSET_OWNER => 'SPA');

複制

3). 轉化AWR資料中的SQL資料，将其中的SQL載入到SQL Set中

DECLARE
  SQLSET_CUR DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN SQLSET_CUR FOR
    SELECT VALUE(P) FROM TABLE(
           DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( 16, 24,
                        'PARSING_SCHEMA_NAME NOT IN (''SYS'', ''SYSTEM'')',
                        NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P;
  DBMS_SQLTUNE.LOAD_SQLSET(
               SQLSET_NAME => 'ORCL_SQLSET_201806',
               SQLSET_OWNER => 'SPA',
               POPULATE_CURSOR => SQLSET_CUR,
               LOAD_OPTION => 'MERGE',
               UPDATE_OPTION => 'ACCUMULATE');
  CLOSE SQLSET_CUR;
    END;
/

複制

4). 打包SQL Set

DROP TABLE SPA.JYZHAO_SQLSETTAB_20180106;
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET ('ORCL_STSTAB_201806', 'SPA', 'SYSAUX');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET ( -
                  SQLSET_NAME          => 'ORCL_SQLSET_201806', -
                  SQLSET_OWNER         => 'SPA', -
                  STAGING_TABLE_NAME   => 'ORCL_STSTAB_201806', -
                  STAGING_SCHEMA_OWNER => 'SPA');

複制

2.4 生産端：導出資料

1). 在作業系統中，導出打包後的SQL Set資料

cat > ./export_sqlset_201806.par <<EOF
USERID='SPA/SPA'
FILE=ORCL_STSTAB_201806.dmp
LOG=exp_spa_sqlset_201806.log
TABLES=ORCL_STSTAB_201806
DIRECT=N
BUFFER=10240000
STATISTICS=NONE
EOF

複制

注意：這裡DIRECT=Y參數在遇到問題後嘗試改為了DIRECT=N，預設也是N。

set NLS_LANG=AMERICAN_AMERICA.US7ASCII
exp PARFILE=export_sqlset_201806.par

複制

注意：NLS_LANG變量是Oracle的變量，設定字元集和資料庫字元集一緻，避免發生錯誤轉換。

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

将 ORCL_STSTAB_201806.dmp 傳輸到 目标伺服器 /orabak/spa下。

2.5 測試端：環境準備

conn / as sysdba
CREATE USER SPA IDENTIFIED BY SPA DEFAULT TABLESPACE SYSAUX;
GRANT DBA TO SPA;
GRANT ADVISOR TO SPA;
GRANT SELECT ANY DICTIONARY TO SPA;
GRANT ADMINISTER SQL TUNING SET TO SPA;

複制

2.6 測試端：測試準備

在進行SPA測試前需要準備測試環境，包括導入生産庫中的SQL Set，對其進行解包(unpack)操作，并建立SPA分析任務。

1). 在作業系統中，執行導入指令，導入SQL Set表

cat > ./import_sqlset_201806.par <<EOF
USERID='SPA/SPA'
FILE=ORCL_STSTAB_201806.dmp
LOG=imp_spa_sqlset_201806.log
FULL=Y
EOF

export NLS_LANG=AMERICAN_AMERICA.US7ASCII
imp PARFILE=import_sqlset_201806.par

複制

2). 解包(unpack)SQL Set

conn SPA/SPA
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (-
                  SQLSET_NAME          => 'ORCL_SQLSET_201806', -
                  SQLSET_OWNER         => 'SPA', -
                  REPLACE              => TRUE, -
                  STAGING_TABLE_NAME   => 'ORCL_STSTAB_201806', -
                  STAGING_SCHEMA_OWNER => 'SPA');

複制

3). 建立SPA分析任務

VARIABLE SPA_TASK  VARCHAR2(64);
EXEC :SPA_TASK := DBMS_SQLPA.CREATE_ANALYSIS_TASK(  -
                             TASK_NAME    => 'SPA_TASK_201806', -
                             DESCRIPTION  => 'SPA Analysis task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), -
                             SQLSET_NAME  => 'ORCL_SQLSET_201806', -
                             SQLSET_OWNER => 'SPA');

複制

2.7 測試端：前期性能

在測試伺服器中，可以直接從SQL Tuning Set中轉化得到所有SQL在10g資料庫中的執行效率，得到10g中的SQL Trail。

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                TASK_NAME      => 'SPA_TASK_201806', -
                EXECUTION_NAME => 'EXEC_10G_201806', -
                EXECUTION_TYPE => 'CONVERT SQLSET', -
                EXECUTION_DESC => 'Convert 10g SQLSET for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

複制

2.8 測試端：後期性能

在測試伺服器（運作11g資料庫）中，需要在本地資料庫（11g）測試運作SQL Tuning Set中的SQL語句，分析所有語句在11g環境中的執行效率，得到11g中的SQL Trail。

vi spa2.sh
 
echo "WARNING: SPA2 Start @`date`"
sqlplus SPA/SPA << EOF!
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                TASK_NAME      => 'SPA_TASK_201806', -
                EXECUTION_NAME => 'EXEC_11G_201806', -
                EXECUTION_TYPE => 'TEST EXECUTE', -
                EXECUTION_DESC => 'Execute SQL in 11g for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
exit
EOF!
echo "WARNING:SPA2 OK @`date`"
 
nohup sh spa2.sh &

複制

2.9 測試端：性能對比

得到兩次SQL Trail之後，可以對比兩次Trial之間的SQL執行性能，可以從不同的次元對兩次Trail中的所有SQL進行對比分析，主要關注的次元有：SQL執行時間，SQL執行的CPU時間，SQL執行的邏輯讀。

1). 對比兩次Trail中的SQL執行時間

conn SPA/SPA
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                TASK_NAME      => 'SPA_TASK_201806', -
                EXECUTION_NAME => 'COMPARE_ET_201806', -
                EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
                EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
                                                 'COMPARISON_METRIC', 'ELAPSED_TIME', -
                                                 'EXECUTE_FULLDML', 'TRUE', -
                                                 'EXECUTION_NAME1','EXEC_10G_201806', -
                                                 'EXECUTION_NAME2','EXEC_11G_201806'), -
                EXECUTION_DESC => 'Compare SQLs between 10g and 11g at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

複制

2). 對比兩次Trail中的SQL執行的CPU時間

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                TASK_NAME      => 'SPA_TASK_201806', -
                EXECUTION_NAME => 'COMPARE_CT_201806', -
                EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
                EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
                                                 'COMPARISON_METRIC', 'CPU_TIME', -
                                                 'EXECUTION_NAME1','EXEC_10G_201806', -
                                                 'EXECUTION_NAME2','EXEC_11G_201806'), -
                EXECUTION_DESC => 'Compare SQLs between 10g and 11g at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

複制

3). 對比兩次Trail中的SQL執行的邏輯讀

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                TASK_NAME      => 'SPA_TASK_201806', -
                EXECUTION_NAME => 'COMPARE_BG_201806', -
                EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
                EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
                                                 'COMPARISON_METRIC', 'BUFFER_GETS', -
                                                 'EXECUTION_NAME1','EXEC_10G_201806', -
                                                 'EXECUTION_NAME2','EXEC_11G_201806'), -
                EXECUTION_DESC => 'Compare SQLs between 10g and 11g at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

複制

2.10 測試端：彙總報告

執行對比分析任務之後，就可以取出對應的對比分析任務的結果報告，主要關注的報告類型有：彙總SQL報告，錯誤SQL報告以及不支援SQL報告。

a) 擷取執行時間全部報告

conn SPA/SPA
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_201806','HTML','ALL','ALL',NULL,1000,'COMPARE_ET_201806')).GETCLOBVAL(0,0) FROM DUAL;
spool off

複制

b) 擷取執行時間下降報告

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_201806','HTML','REGRESSED','ALL',NULL,1000,'COMPARE_ET_201806')).GETCLOBVAL(0,0) FROM DUAL;
spool off

複制

c) 擷取邏輯讀全部報告

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_201806','HTML','ALL','ALL',NULL,1000,'COMPARE_BG_201806')).GETCLOBVAL(0,0) FROM DUAL;
spool off

複制

d) 擷取邏輯讀下降報告

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_201806','HTML','REGRESSED','ALL',NULL,1000,'COMPARE_BG_201806')).GETCLOBVAL(0,0) FROM DUAL;
spool off

複制

e) 擷取錯誤報告

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_201806','HTML','ERRORS','ALL',NULL,1000,'COMPARE_ET_201806')).GETCLOBVAL(0,0) FROM DUAL;
spool off

複制

f) 擷取不支援報告

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_201806','HTML','UNSUPPORTED','ALL',NULL,1000,'COMPARE_ET_201806')).GETCLOBVAL(0,0) FROM DUAL;
spool off

複制

g) 擷取執行計劃變化報告

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_201806','HTML','CHANGED_PLANS','ALL',NULL,1000,'COMPARE_ET_201806')).GETCLOBVAL(0,0) FROM DUAL;
spool off

複制

3.SPA環境清理

3.1 檢視SQLSET

conn SPA/SPA
select owner,name,STATEMENT_COUNT from dba_sqlset;

複制

3.2 檢視分析任務

select owner,task_id,task_name,created,LAST_MODIFIED,STATUS from DBA_ADVISOR_TASKS  where task_name like upper('%&task_name%') order by 2;
SPA_TASK_201806

複制

3.3 删除ANALYSIS_TASK

exec dbms_sqlpa.DROP_ANALYSIS_TASK('SPA_TASK_201806');

複制

3.4 删除sqlset

exec dbms_sqltune.DROP_SQLSET('ORCL_SQLSET_201806');

複制

如果删除時出現異常情況"ORA-13757",提示STS是活動的,可以嘗試使用下面SQL修改後再進行删除。

delete from wri$_sqlset_references
    where sqlset_id in (select id
    from wri$_sqlset_definitions
    where name in ('ORCL_SQLSET_201806','ORCL_SQLSET_201806'));
commit;

複制

3.5 删除使用者

删除SPA使用者（兩端）

drop user spa cascade;

複制

上一篇: 【Java】Java Controller 每次隻能一個請求 多線程 ReentrantLock1.概述
下一篇: Oracle SPA取報告階段xml解析失敗解決方案