SPA采集到的SQLSET約500余萬條SQL，需要生成buffer gets、cpu time、elapsed_time報告，此報告只列出top 300的記錄，另外還需要生成error和unsupport報告。
生成報告之前需要做的準備工作如下：
要執行SPA分析升級前后性能，需要導入10g統計信息，對于統計信息的處理有3種：
一般1）和2）是常用的升級統計信息處理方式，如果10g統計信息有大量表存在問題，推薦使用2），所以在做SPA分析前，可以對統計信息完整性進行檢查（未收集表、收集但是丟失了如列、索引、分區等統計信息、統計信息過舊等），從而確定最佳方案。
（1）捕獲生產環境SQL Tuning Sets,這個在第3節：SPA采集中已經說明。
（2）傳輸SQL Tuning Sets,將10g上的STS通過諸如exp/imp,expdp/impdp等工具導入到11g待分析SPA環境中。
（3）執行10g分析：這步驟很快
（4）執行11g分析：這步驟根據SQL不同，耗時不同，庫20w條左右SQL SET，大約耗時24小時。
（5）生成報告
（6）報告迭代：在執行3）到5）之前，由于STS數量很大，為了提高報告生成效率，需要進行SQL SET分割處理，之后生成報告，在分析報告的過程中，可能涉及到修改全局參數，這樣，修改完參數后，還需要進行SPA分析，因此3）到5）的步驟是個迭代的過程。
（7）實施：整理分析結果，進行生成實施。
4.1 10g STS導入到11g中
10g STS以表形式導出，并導入到11g中，這需要在10g中對STS進行pack打包到一中轉表中，然后導入到11g后，再unpack解包。
Pack過程：以a庫為例，將多個sqlset導入到一個中轉表中,可以將腳本用shell放后臺執行(可以并行執行5個，多了會報ORA-01555錯誤)
--a庫 sqlseta1_tab1到tab20
BEGIN
DBMS_SQLTUNE.create_stgtab_sqlset(table_name => STS_TAB_A_0922,
schema_name => SPA,
tablespace_name => SYSAUX);
END;
/
--pack
--a1
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => sqlseta1_tab1,
sqlset_owner => SPA,
staging_table_name => STS_TAB_A_0922,
staging_schema_owner => SPA);
END;
/
。。。
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => sqlseta1_tab20,
sqlset_owner => SPA,
staging_table_name => STS_TAB_A_0922,
staging_schema_owner => SPA);
END;
/
--a2
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => sqlseta2_tab1,
sqlset_owner => SPA,
staging_table_name => STS_TAB_A_0922,
staging_schema_owner => SPA);
END;
/
。。。
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => sqlseta2_tab8,
sqlset_owner => SPA,
staging_table_name => STS_TAB_A_0922,
staging_schema_owner => SPA);
END;
/
Pack完畢后進行剔除處理：
剔除一些諸如sqlplus,plsql dev等執行的SQL、以及按照FORCE_MATCHING_SIGNATURE剔除重復行、剔除insert into values等。
查看對應SQL類型的數據分布，后續按照數據分布情況分割STS：
--command_type對應含義可以查詢V$SQLCOMMAND
SQL> select count(*),command_type from SPA.STS_TAB_A_0922 group by command_type;
COUNT(*) COMMAND_TYPE
---------- ------------
2469 7 --DELETE
27506 47 --PL/SQL EXECUTE
11 170 --CALL METHOD
169548 6 --UPDATE
2204682 3 --SELECT
253970 2 --INSERT
執行剔除：
alter session enable parallel dml;
delete/*+parallel(8)*/ from SPA.STS_TAB_A_0922 where MODULE=PL/SQL Developer;
commit;
delete/*+parallel(8)*/ from SPA.STS_TAB_A_0922 where MODULE=plsqldev.exe;
commit;
delete/*+parallel(8)*/ from SPA.STS_TAB_A_0922 where MODULE like sqlplus%;
commit;
delete/*+parallel(8)*/ from SPA.STS_TAB_A_0922 where MODULE like SQL*PLUS;
commit;
delete/*+parallel(8)*/ from SPA.STS_TAB_A_0922 a where rowid !=(select max(rowid) from SPA.STS_TAB_A_0922 b where a.FORCE_MATCHING_SIGNATURE=b.FORCE_MATCHING_SIGNATURE) and a.FORCE_MATCHING_SIGNATURE<>0;
commit;
delete/*+parallel(8)*/ from SPA.STS_TAB_A_0922 where upper(sql_text) like %INSERT%INTO%VALUES%;
commit;
--刪除command_type in (48,1,189) --SET TRANSACTION --CREATE TABLE --ALTER TABLESPACE
delete/*+parallel(8)*/ from SPA.STS_TAB_A_0922 where command_type in (48,1,189);
commit;
10g導出STS：
export NLS_LANG=American_America.zhs16gbk
expspa/spa tables=spa.STS_TAB_A_0922 file=/arch02/STS_TAB_A_0922.dmp log=STS_TAB_A_0922.log
導入STS到11g中：
imp spa/spa fromuser=spa touser=spa file=/oradata01/STS_TAB_A_0922.dmp feedback=100
4.2 11g 500w條記錄的STS 分割
由于采集到的STS數量很大，需要分割STS，每個STS中存放20w條左右的SQL最佳，另外按照語句類型進行分割處理，這樣SPA分析報告可以對指定的STS進行并行分析，確保1到2天內能夠完成報告生成，提高報告生成效率。
1）分割STS
將STS均分，UPDATE+DELETE 1份，PL/SQL EXECUTE+CALL METHOD 份，INSERT多帶帶1份，SELECT均分為10份，共13個STS。
--先查詢總數量
SQL> select count(*) from STS_TAB_A_0922;
COUNT(*)
--------------
2658186
--查詢各類型SQL占的數量
select b.command_type,b.command_name,count(*)
from STS_TAB_A_0922 a,v$sqlcommand b
where a.command_type=b.command_type
group by b.command_type,b.command_name;
COMMAND_TYPE COMMAND_NAME COUNT(*)
------------------------ ---------------------------------------------------------------- --------------
3 SELECT 2204682
47 PL/SQL EXECUTE 27506
2 INSERT 253970
7 DELETE 2469
6 UPDATE 169548
170 CALL METHOD 11
--建立索引，提高后續處理效率
CREATE INDEX IDX_STS_TAB_A_0922 ON STS_TAB_A_0922(SQL_ID) PARALLEL 16;
--將SELECT語句對應STS進行拆分為10份，每份22w條左右
DECLARE
L_CURR_TABLE_TIPS NUMBER :=0;
BEGIN
--SELECT 0..9尾號 改為SQLSET_RUN
FOR X IN (SELECT SQL_ID FROM STS_TAB_A_0922 where command_type =3 ORDER BY ELAPSED_TIME/EXECUTIONS) LOOP
UPDATE STS_TAB_A_0922 SET NAME=SQLSET_RUN_||L_CURR_TABLE_TIPS WHERE SQL_ID = X.SQL_ID;
L_CURR_TABLE_TIPS := MOD(L_CURR_TABLE_TIPS + 1, 10);
END LOOP;
END;
/
--UPDATE+DELETE SQLSET_RUN_10
UPDATE STS_TAB_A_0922 SET NAME=SQLSET_RUN_10 where command_type in (6,7);
--PL/SQL EXECUTE+CALL METHOD
UPDATE STS_TAB_A_0922 SET NAME=SQLSET_RUN_11 where command_type in (47,170);
--INSERT
UPDATE STS_TAB_A_0922 SET NAME=SQLSET_RUN_12 where command_type in (2);
COMMIT;
--查詢SELECT對應數量
select name,count(*)
from STS_TAB_A_0922
where command_type=3
group by name;
NAME COUNT(*)
-------------------------------- ---------------
SQLSET_RUN_9 220468
SQLSET_RUN_1 220469
SQLSET_RUN_5 220468
SQLSET_RUN_6 220468
SQLSET_RUN_7 220468
SQLSET_RUN_3 220468
SQLSET_RUN_2 220468
SQLSET_RUN_0 220469
SQLSET_RUN_8 220468
SQLSET_RUN_4 220468
2）生成批量創建和刪除并行SQL Set Table的語句
用SPA用戶執行,開多個窗口，每個要10分鐘
--共13個SQLSET要處理
set line 9999 pagesize 9999
select create table SQLSET_TAB_RUN_||(ROWNUM-1)||
NESTED TABLE "BIND_LIST" STORE AS "SQLSET_TAB_RUN_B_||(ROWNUM-1)||"
NESTED TABLE "PLAN" STORE AS "SQLSET_TAB_RUN_P_||(ROWNUM-1) || "
as select * from STS_TAB_A_0922 where name=SQLSET_RUN_||(ROWNUM-1)||; x
FROM dba_objects where rownum <= 13;
3）檢查數量
select SELECT SQLSET_TAB_RUN_||(level-1)|| name,count(*)
FROM SQLSET_TAB_RUN_||(level-1)|| UNION ALL
from dual
connect by level<=13;
NAME COUNT(*)
-------------------------------- ---------------
SQLSET_TAB_RUN_0 220469
SQLSET_TAB_RUN_1 220469
SQLSET_TAB_RUN_2 220468
SQLSET_TAB_RUN_3 220468
SQLSET_TAB_RUN_4 220468
SQLSET_TAB_RUN_5 220468
SQLSET_TAB_RUN_6 220468
SQLSET_TAB_RUN_7 220468
SQLSET_TAB_RUN_8 220468
SQLSET_TAB_RUN_9 220468
SQLSET_TAB_RUN_10 172017
SQLSET_TAB_RUN_11 27517
SQLSET_TAB_RUN_12 253970
13 rows selected.
4）unpack sqlset
可以并行執行5個，多了會報ORA-01555錯誤。可以寫成SHELL腳本放后臺執行。
DECLARE
X NUMBER :=0;
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
SQLSET_NAME => SQLSET_RUN_||X,
SQLSET_OWNER => SPA,
REPLACE => TRUE,
STAGING_TABLE_NAME => SQLSET_TAB_RUN_||X,
STAGING_SCHEMA_OWNER => SPA);
END;
/
。。。
DECLARE
X NUMBER :=12;
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
SQLSET_NAME => SQLSET_RUN_||X,
SQLSET_OWNER => SPA,
REPLACE => TRUE,
STAGING_TABLE_NAME => SQLSET_TAB_RUN_||X,
STAGING_SCHEMA_OWNER => SPA);
END;
/
4.3 SPA分析需要的統計信息導入
測試環境中，準確的統計信息是運行SPA測試的基礎。先確定統計信息方案，等確定方案后，導入10g統計信息到11g,導入后可能還需要做一些處理，比如更新統計信息等。實際上，在SPA分析過程中，因為統計信息問題導致2次SPA分析迭代，第1次：直接導入10g統計信息，由于發現大量表統計信息有問題，缺失列信息，統計信息過舊等。后面確認采用導入10g統計信息后重新收集：
生產庫未收集統計信息的表，除非出現大的性能問題，確認后收集。
生產庫收集了統計信息的表，如果統計信息不完整，比如列或索引等缺失，則收集。采用method_opt => FOR ALL COLUMNS SIZE REPEAT，可以保證原先沒有直方圖的采用for all columns size 1,有直方圖的更新直方圖，estimate_percent默認。
統計信息處理流程如下：
將生產中需要的業務SCHEMA對象統計信息導出，然后傳輸到測試環境中，注意傳到測試環境中，首先需要刪除原有的統計信息，否則可能出現不一致的問題。流程和腳本如下：
1）從10g生產環境導出統計信息
--ogg.ogg_userlist存放需要導出的業務用戶名，用來拼導出腳本
select exec DBMS_STATS.EXPORT_SCHEMA_STATS(ownname=>||upper(username)||, stattab=>STAT_SNC_10G_20140916,statown=>SPA, statid=>||upper(username)||); from ogg.ogg_userlist;
--a庫 exportstat.sh
echo start `date`
sqlplus / as sysdba <exec DBMS_STATS.EXPORT_SCHEMA_STATS(ownname=>AAAA, stattab=>STAT_SNC_10G_20140916,statown=>SPA, statid=>AAAA);
…省略
exec DBMS_STATS.EXPORT_SCHEMA_STATS(ownname=>TEST, stattab=>STAT_SNC_10G_20140916,statown=>SPA, statid=>TEST);
exit;
EOF
echo end `date`
--后臺執行
nohup ./exportstat.sh > exportstat.sh.log 2>&1 &
2）刪除11g測試環境統計信息
select exec DBMS_STATS.DELETE_SCHEMA_STATS(ownname=>||upper(username)||, force=>true, no_invalidate=>false); from ogg.ogg_userlist;
exec DBMS_STATS.DELETE_SCHEMA_STATS(ownname=>AAAA, force=>true, no_invalidate=>false);
…省略
exec DBMS_STATS.DELETE_SCHEMA_STATS(ownname=>TEST, force=>true, no_invalidate=>false);
3）導入10g統計信息到11g測試環境中
-- 升級10g統計信息為11g,a,b庫都做,表結構不同
exec DBMS_STATS.UPGRADE_STAT_TABLE(ownname=>SPA, stattab=>STAT_SNC_10G_20140916);
--導入，可編寫shell腳本后臺執行
--a庫
--select exec DBMS_STATS.IMPORT_SCHEMA_STATS(ownname=>||upper(username)||, stattab=>STAT_SNC_10G_20140916,statown=>SPA, statid=>||upper(username)||, force=>true ,no_invalidate=>false); from ogg.ogg_userlist;
exec DBMS_STATS.IMPORT_SCHEMA_STATS(ownname=>AAAA, stattab=>STAT_SNC_10G_20140916,statown=>SPA, statid=>AAAA, force=>true ,no_invalidate=>false);
…省略
exec DBMS_STATS.IMPORT_SCHEMA_STATS(ownname=>TEST, stattab=>STAT_SNC_10G_20140916,statown=>SPA, statid=>TEST, force=>true ,no_invalidate=>false);
在最終升級的統計信息方案是導入10g統計信息到11g后，對10g未收集的不收集，收集的采用for all columns size repeat更新，詳細參考“最終升級統計信息方案”。
4.4 創建并行分析任務生成報告
通過對STS進行拆分，可以實現SPA并行分析，SPA分析主要有：SPA任務創建、生成10g Trail,生成11g Trail,生成對比分析報告。
1）創建并行SPA分析任務
此步驟很快，小于10s鐘。
conn spa/spa
DECLARE
L_SPA_TASK_NAME VARCHAR2(64);
BEGIN
FOR X IN 0..12 LOOP
L_SPA_TASK_NAME := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
TASK_NAME => SPA_TASK_RUN_||X,
DESCRIPTION => SPA Analysis task at : ||TO_CHAR(SYSDATE, YYYY-MM-DD HH24:MI:SS),
SQLSET_NAME => SQLSET_RUN_||X,
SQLSET_OWNER => SPA);
DBMS_OUTPUT.PUT_LINE(SPA Task Created as : ||L_SPA_TASK_NAME);
END LOOP;
END;
/
--查詢任務是否創建
select owner,task_name from DBA_ADVISOR_TASKS where task_name like SPA_TASK_RUN_%;
2）生成10g Trail
由于10g SQL執行信息已經采集到，所以此過程很快，小于10s，使用shell并行執行。主要參數用EXECUTION_TYPE指定為CONVERT SQLSET。
i=0
while [ "$i" -le 12 ]
do
cat > ./exec_SPA_RUN_$i.sh <sqlplus spa/spa <EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( TASK_NAME => SPA_TASK_RUN_||$i,EXECUTION_NAME => EXEC_10G_RUN_||$i,EXECUTION_TYPE => CONVERT SQLSET,EXECUTION_DESC => Convert 10g SQLSET for SPA Task at : ||TO_CHAR(SYSDATE, YYYY-MM-DD HH24:MI:SS));
exit
EOF
EOFSCRIPT
chmod u+x exec_SPA_RUN_$i.sh
nohup ./exec_SPA_RUN_$i.sh > exec_SPA_RUN_$i.log 2>&1 &
i=$((i+1))
done
--查詢執行情況以及是否有錯誤
set line 300 pagesize 9999
col STATUS_MESSAGE for a50;
col ERROR_MESSAGE for a50;
alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss;
select TASK_ID,TASK_NAME,EXECUTION_NAME,EXECUTION_START,EXECUTION_END,STATUS_MESSAGE,ERROR_MESSAGE from DBA_ADVISOR_EXECUTIONS where task_name like SPA_TASK_RUN_%;
select MESSAGE,COUNT(*) FROM DBA_ADVISOR_FINDINGS WHERE TYPE=ERROR GROUP BY MESSAGE ORDER BY 2;
3）生成11g Trail
并行測試生成11g的執行信息,生成shell腳本，后臺執行。這個過程最為關鍵的，也是最慢的過程，ORACLE會實際執行STS中對應SQL，有可能有的SQL執行計劃改變，會執行的很慢，從而影響整理過程。最容易出問題的步驟就在這個過程中，因此，需要在執行過程中進行監控和分析、甚至需要迭代重跑并行分析任務。主要參數用EXECUTION_TYPE指定為TEST EXECUTE。
i=0
while [ "$i" -le 12 ]
do
cat > ./exec_SPA_RUN_$i.sh <sqlplus spa/spa <EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(SPA_TASK_RUN_$i, TEST EXECUTE, EXEC_11G_RUN_$i, NULL,
Execute SQL in 11g for SPA Task at : ||TO_CHAR(SYSDATE, YYYY-MM-DD HH24:MI:SS));
exit
EOF
EOFSCRIPT
chmod u+x exec_SPA_RUN_$i.sh
nohup ./exec_SPA_RUN_$i.sh > exec_SPA_RUN_$i.log 2>&1 &
i=$((i+1))
done
4）檢查SPA分析任務進度和狀態
由于第3步：生成11g Trail是非常耗時（庫如果不進行STS拆分，需耗時13天左右）也是容易出問題的步驟，因此，需要在工作日，隔一小時查看下進度，并查看是否有報錯。
set line 300 pagesize 999
col task_name for a20
col fin_ratio for a5
alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss;
SELECT SID, TASK_ID,(select distinct task_name from DBA_ADVISOR_EXECUTIONS b where a.task_id=b.task_id) task_name,SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK,2)*100||% fin_ratio,
ELAPSED_SECONDS,LAST_UPDATE_TIME,START_TIME, START_TIME + (SYSDATE - START_TIME)/SOFAR * TOTALWORK EST_END_TIME
FROM V$ADVISOR_PROGRESS a
WHERE TASK_ID IN (SELECT TASK_ID FROM DBA_ADVISOR_EXECUTIONS where task_name like SPA_TASK%)
AND SOFAR <> TOTALWORK
AND SOFAR <> 0
ORDER BY 2;
----日志查詢，查看出錯信息
set line 300 pagesize 9999
col status_message for a10
col error_message for a50
alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss;
select task_name,execution_start,execution_end,status,status_message,error_message from DBA_ADVISOR_LOG where TASK_NAME LIKE SPA%;
select TASK_ID,TASK_NAME,EXECUTION_NAME,EXECUTION_START,EXECUTION_END,STATUS_MESSAGE,ERROR_MESSAGE from DBA_ADVISOR_EXECUTIONS where task_name like SPA_TASK_RUN% order by EXECUTION_START desc;
5）生成SPA分析報告
通過對比10g和11g SQL執行統計信息:buffer gets、cpu time、elapsed time、plan_hash_value等來獲得執行計劃是否改變、SQL性能是否下降報表。
并行執行分析過程并產生報告（Shell環境中執行，最好建立一個新的目錄spareport）,大約1小時。
cd /home/oracle/spa
mkdir spareport
腳本如下，放入后臺執行
i=0
while [ "$i" -le 12 ]
do
cat > ./get_RPT_$i.sh <sqlplus spa/spa <-------------elapsed_time
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => SPA_TASK_RUN_$i,
execution_type => COMPARE PERFORMANCE,
execution_name => Compare_elapsed_time,
execution_params => dbms_advisor.arglist(execution_name1, EXEC_10G_RUN_$i, execution_name2, EXEC_11G_RUN_$i, comparison_metric, elapsed_time) );
end;
/
-------------cpu_time
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => SPA_TASK_RUN_$i,
execution_type => COMPARE PERFORMANCE,
execution_name => Compare_CPU_time,
execution_params => dbms_advisor.arglist(execution_name1, EXEC_10G_RUN_$i, execution_name2, EXEC_11G_RUN_$i, comparison_metric, CPU_TIME) );
end;
/
-------------buffer_gets
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => SPA_TASK_RUN_$i,
execution_type => COMPARE PERFORMANCE,
execution_name => Compare_BUFFER_GETS_time,
execution_params => dbms_advisor.arglist(execution_name1, EXEC_10G_RUN_$i, execution_name2, EXEC_11G_RUN_$i, comparison_metric, BUFFER_GETS) );
end;
/
ALTER SESSION SET EVENTS=31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400;
-------------report
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
spool spa_report_elapsed_time_$i.html
SELECT dbms_sqlpa.report_analysis_task(SPA_TASK_RUN_$i, HTML, ALL,ALL, top_sql=>300,execution_name=>Compare_elapsed_time) FROM dual;
spool off;
spool spa_report_CPU_time_$i.html
SELECT dbms_sqlpa.report_analysis_task(SPA_TASK_RUN_$i, HTML, ALL,ALL, top_sql=>300,execution_name=>Compare_CPU_time) FROM dual;
spool off;
spool spa_report_buffer_$i.html
SELECT dbms_sqlpa.report_analysis_task(SPA_TASK_RUN_$i,HTML,ALL,ALL,top_sql=>300,execution_name=>Compare_BUFFER_GETS_time) FROM dual;
spool off;
spool spa_report_errors_$i.html
SELECT dbms_sqlpa.report_analysis_task(SPA_TASK_RUN_$i, HTML, errors,summary) FROM dual;
spool off;
spool spa_report_unsupport_$i.html
SELECT dbms_sqlpa.report_analysis_task(SPA_TASK_RUN_$i, HTML, unsupported,all) FROM dual;
spool off;
exit
EOF
EOFSCRIPT
chmod u+x get_RPT_$i.sh
#nohup ./get_RPT_$i.sh >get_RPT_$i.log 2>&1 &
i=$((i+1))
done
4.5 SPA分析的迭代過程
SPA分析經過多次迭代，每迭代一次，則4.4的步驟就要重新做一遍，只需要修改對應的TASK_NAME即可，如果找到正確的方法，可以減少迭代次數。每次迭代都是由于經過SPA分析發現問題，不得不修改如影響全局的優化器參數、打開或關閉fix control開關、收集大量表的統計信息等，主要由于影響較大，所以需要重跑SPA分析。
第一次迭代：
設置參數_optimizer_squ_bottomup=true和_optimizer_cost_based_transformation=LINEAR。
第二次迭代：
將"_fix_control"增加9380298:ON，加上原有的開關，執行語句：
alter system set "_fix_control"=9380298:ON,8560951:ON,8893626:OFF,9344709:OFF,9195582:OFF;
第三次迭代：
導入10g統計信息到11g庫中后，對10g原先不收集的表還是不收集，已收集的表通過method_opt=>’for all columns size repeat’更新。
其它迭代：
由于執行過程中報ORA-01555錯誤。見4.6.SPA執行分析過程注意點。
4.6 SPA執行分析過程注意點
SPA執行過程中，某些SQL可能因為執行計劃改變或者數據量變化，導致執行超時或報錯（ORA-01555等），這時，SPA分析可能終止，需要找出對應SQL，從STS中清除出去，多帶帶分析或設置超時。如下：
查詢超時SQL
select b.sql_id
from DBA_ADVISOR_FINDINGS a,dba_advisor_sqlstats b
where a.task_id=b.task_id and a.object_id=b.object_id
and a.TYPE=ERROR
and a.message like %The current operation was interrupted because it timed out%;
--導致ora-01555錯誤的sql,暫且刪除
EXEC DBMS_SQLTUNE.DELETE_SQLSET(SQLSET_RUN_3,sql_id=5r5jth1k2prdr,SPA);
--修改undo
alter tablespace undo add datafile .... size 8192M AUTOEXTEND OFF;
alter system set undo_retention=10000;
報ORA-01555錯誤，除了undo設置以外，還可能是執行時間超長，可以對執行TASK設置超時，當某個SQL超出XX秒后，則自動結束，這個步驟，需要在生成11g Trial之前做，如下：
--設置超時時間 很重要，有的特別長的，超時跳過，防止ORA-01555
exec DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name=>SPA_TASK_RUN_0,parameter=>LOCAL_TIME_LIMIT,value=>2000);
exec DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name=>SPA_TASK_RUN_1,parameter=>LOCAL_TIME_LIMIT,value=>3000);
exec DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name=>SPA_TASK_RUN_3,parameter=>LOCAL_TIME_LIMIT,value=>3000);
exec DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name=>SPA_TASK_RUN_4,parameter=>LOCAL_TIME_LIMIT,value=>3000);
exec DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name=>SPA_TASK_RUN_6,parameter=>LOCAL_TIME_LIMIT,value=>3000);
exec DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name=>SPA_TASK_RUN_7,parameter=>LOCAL_TIME_LIMIT,value=>3000);
SPA分析視圖主要以DBA/USER_SQLSET開頭的系列視圖以及DBA/USER_ADVISOR開頭的系列視圖，主要查詢語句如下：
STS常用查詢如下：
--查詢sts中采集sql數目
select name,owner,to_char(last_modified,yyyy-mm-dd hh24:mi:ss) last_modify,statement_count cnt from dba_sqlset;
--查詢對應sql以及執行信息，如buffer gets
select * from dba_sqlset_statements where sql_id=8v4dradbvqqy5;
--獲取綁定變量
select dbms_sqltune.extract_binds(bind_data) from dba_sqlset_statements where sql_id=8v4dradbvqqy5;
--dbms_sqltune.extract_binds獲取綁定變量字段信息，關注name,position,datatype_string,value_string
name VARCHAR2(30), /* bind variable name */
position NUMBER, /* position of bind in sql statement */
dup_position NUMBER, /* if any, position of primary bind variable */
datatype NUMBER, /* datatype id for this bind */
datatype_string VARCHAR2(15),/* string representation of above datatype */
character_sid NUMBER, /* character set id if bind is NLS */
precision NUMBER, /* bind precision */
scale NUMBER, /* bind scale */
max_length NUMBER, /* maximum bind length */
last_captured DATE, /* DATE when this bind variable was captured */
value_string VARCHAR2(4000), /* bind value (text representation) */
value_anydata ANYDATA) /* bind value (anydata representation) */
在實際根據SPA報告分析性能下降原因時，可以通過以上腳本查詢出SQL以及對應綁定變量,這樣方便進行性能分析，直接使用SPA報告中的SQL，經常因為空格等原因會報錯。
SPA分析常用查詢如下：
--檢查并行運行的SPA任務的狀態
SELECT SID, TASK_ID,(select distinct task_name from DBA_ADVISOR_EXECUTIONS b where a.task_id=b.task_id) task_name,SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK,2)*100||% fin_ratio,
ELAPSED_SECONDS,LAST_UPDATE_TIME,START_TIME, START_TIME + (SYSDATE - START_TIME)/SOFAR * TOTALWORK EST_END_TIME
FROM V$ADVISOR_PROGRESS a
WHERE TASK_ID IN (SELECT TASK_ID FROM DBA_ADVISOR_EXECUTIONS where task_name like SPA_TASK%)
AND SOFAR <> TOTALWORK
AND SOFAR <> 0
ORDER BY 2;
--日志查詢，查看出錯信息
select task_name,execution_start,execution_end,status,status_message,error_message from DBA_ADVISOR_LOG where TASK_NAME LIKE SPA%;
select TASK_ID,TASK_NAME,EXECUTION_NAMEs,EXECUTION_START,EXECUTION_END,STATUS_MESSAGE,ERROR_MESSAGE from DBA_ADVISOR_EXECUTIONS where task_name like SPA_TASK_RUN% order by EXECUTION_START desc;
--查詢出錯SQL_ID
select b.sql_id
from DBA_ADVISOR_FINDINGS a,dba_advisor_sqlstats b
where a.task_id=b.task_id and a.object_id=b.object_id
and a.TYPE=ERROR
and a.message like %ORA-01555%;
6.1 如何分析報告
根據每個STS跑出的SPA分析報告：buffer gets、cpu time、elapsed time、error、unsupport ，分別分析，重點關注buffer gets、cpu time、elapsed time 3份報告，按照順序分析，一般來說，cpu time,elapsed time中出現的SQL，基本都在buffer gets中。報告樣式如下：
標題頭：關注status 是否是COMPLETED,關注其他是否正常，比如SQL語句數目，出錯信息對應語句可以用SPA常見查詢，查詢出錯SQL_ID。
匯總信息：查看SPA分析的匯總情況，總量，多少性能提高的，多少計劃改變的，未變的，出錯的數量
明細信息：SPA列出TOP 300的信息，重點關注。有object_id,sql_id,執行負載，10g執行頻率，執行前對應指標度量信息，執行后對應指標度量信息，影響，計劃是否改變。
6.2 需要分析的SQL
對每份報告，首先看報告頭，判斷報告是否正常執行完畢，如果正常，主要分析明細信息。將TOP 300的明細COPY到EXCEL中。然后按照plan change,選擇y的，然后按照Impact on SQL從小到大排序，只關注Impact on SQL值<0的。也就是分析執行計劃改變、性能下降的SQL，由于升級伴隨著導入導出，一般執行計劃未變的，無須分析，除非發現特別慢的，可以分析。最后excel可以增加備注列，說明性能下降原因，以便匯總和解決。
以上excel每個報告一份，并且將buffer_gets、cpu time、elapsed time作為多帶帶的sheet。每個報告需要輸出：
6.3 分析性能下降原因
10.2.0.4升級到11.2.0.4,SQL出現性能下降，側重于分析如下方面：
優化器新特性引入導致的BUG，如Adaptive Cursor sharing/Cardnality feedback，經常存在導致SQL計劃頻繁改變。
優化器新特性引入導致的限制，特別是查詢轉換方面的，如BUG：
9380298 By design JPPD does not consider to push join predicates into a view if this results in a cartesian product between the tables involved in the pushed predicates.(Optimizer trace shows
JPPD: JPPD bypassed: Cartesian product found
)
ORACLE不使用JPPD謂詞推入
參數問題，比如改變參數的默認值，導致在10g中SQL性能很好，但是在11g中不可以。
優化器改變導致執行計劃細微差別，但是本質一致，比如10g是UNION ALL PARTITION,11g是UNION ALL。
統計信息問題：包括原10g統計信息不準確，因為算法差別，到11g中有問題，解決需要重新收集，比如更新過期統計信息、不完整統計信息、需要收集某些列直方圖等。
對象有效性問題：比如11g中索引因某些問題導致失效，需要進行索引有效性檢查。
FIX CONTROL開關問題，引入的很多特性都可以通過FIX CONTROL開關控制。如"_fix_control"可以設置為9380298:ON。
SPA報告不準確，可能數據量變化、或者返回行不同等會造成結果不同，但是SPA報告只對比對應指標性能，而且10g的指標是個平均值,11g是單次SPA測試結果。
