四. SPA 報(bào)告生成：生成對(duì)比性能報(bào)告
SPA采集到的SQLSET約500余萬(wàn)條SQL，需要生成buffer gets、cpu time、elapsed_time報(bào)告，此報(bào)告只列出top 300的記錄，另外還需要生成error和unsupport報(bào)告。
生成報(bào)告之前需要做的準(zhǔn)備工作如下：
構(gòu)造與升級(jí)目標(biāo)庫(kù)相同的測(cè)試環(huán)境。
將10g生產(chǎn)庫(kù)采集到的SQL負(fù)載（STS）傳輸?shù)綔y(cè)試庫(kù)中，包括pack、傳輸、unpack等過(guò)程。
將數(shù)據(jù)庫(kù)中的表盡可能有生產(chǎn)保持一致地傳輸?shù)綔y(cè)試庫(kù)中。
統(tǒng)計(jì)信息處理：
要執(zhí)行SPA分析升級(jí)前后性能，需要導(dǎo)入10g統(tǒng)計(jì)信息，對(duì)于統(tǒng)計(jì)信息的處理有3種：
一般1）和2）是常用的升級(jí)統(tǒng)計(jì)信息處理方式，如果10g統(tǒng)計(jì)信息有大量表存在問(wèn)題，推薦使用2），所以在做SPA分析前，可以對(duì)統(tǒng)計(jì)信息完整性進(jìn)行檢查（未收集表、收集但是丟失了如列、索引、分區(qū)等統(tǒng)計(jì)信息、統(tǒng)計(jì)信息過(guò)舊等），從而確定最佳方案。
（1）捕獲生產(chǎn)環(huán)境SQL Tuning Sets,這個(gè)在第3節(jié)：SPA采集中已經(jīng)說(shuō)明。
（2）傳輸SQL Tuning Sets,將10g上的STS通過(guò)諸如exp/imp,expdp/impdp等工具導(dǎo)入到11g待分析SPA環(huán)境中。
（3）執(zhí)行10g分析：這步驟很快
（4）執(zhí)行11g分析：這步驟根據(jù)SQL不同，耗時(shí)不同，庫(kù)20w條左右SQL SET，大約耗時(shí)24小時(shí)。
（5）生成報(bào)告
（6）報(bào)告迭代：在執(zhí)行3）到5）之前，由于STS數(shù)量很大，為了提高報(bào)告生成效率，需要進(jìn)行SQL SET分割處理，之后生成報(bào)告，在分析報(bào)告的過(guò)程中，可能涉及到修改全局參數(shù)，這樣，修改完參數(shù)后，還需要進(jìn)行SPA分析，因此3）到5）的步驟是個(gè)迭代的過(guò)程。
（7）實(shí)施：整理分析結(jié)果，進(jìn)行生成實(shí)施。
4.1 10g STS導(dǎo)入到11g中
10g STS以表形式導(dǎo)出，并導(dǎo)入到11g中，這需要在10g中對(duì)STS進(jìn)行pack打包到一中轉(zhuǎn)表中，然后導(dǎo)入到11g后，再u(mài)npack解包。
Pack過(guò)程：以a庫(kù)為例，將多個(gè)sqlset導(dǎo)入到一個(gè)中轉(zhuǎn)表中,可以將腳本用shell放后臺(tái)執(zhí)行(可以并行執(zhí)行5個(gè)，多了會(huì)報(bào)ORA-01555錯(cuò)誤)
--a庫(kù) 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完畢后進(jìn)行剔除處理：
剔除一些諸如sqlplus,plsql dev等執(zhí)行的SQL、以及按照FORCE_MATCHING_SIGNATURE剔除重復(fù)行、剔除insert into values等。
查看對(duì)應(yīng)SQL類(lèi)型的數(shù)據(jù)分布，后續(xù)按照數(shù)據(jù)分布情況分割STS：
--command_type對(duì)應(yīng)含義可以查詢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
執(zhí)行剔除：
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導(dǎo)出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
導(dǎo)入STS到11g中：
imp spa/spa fromuser=spa touser=spa file=/oradata01/STS_TAB_A_0922.dmp feedback=100
4.2 11g 500w條記錄的STS 分割
由于采集到的STS數(shù)量很大，需要分割STS，每個(gè)STS中存放20w條左右的SQL最佳，另外按照語(yǔ)句類(lèi)型進(jìn)行分割處理，這樣SPA分析報(bào)告可以對(duì)指定的STS進(jìn)行并行分析，確保1到2天內(nèi)能夠完成報(bào)告生成，提高報(bào)告生成效率。
1）分割STS
將STS均分，UPDATE+DELETE 1份，PL/SQL EXECUTE+CALL METHOD 份，INSERT多帶帶1份，SELECT均分為10份，共13個(gè)STS。
--先查詢總數(shù)量
SQL> select count(*) from STS_TAB_A_0922;
COUNT(*)
--------------
2658186
--查詢各類(lèi)型SQL占的數(shù)量
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
--建立索引，提高后續(xù)處理效率
CREATE INDEX IDX_STS_TAB_A_0922 ON STS_TAB_A_0922(SQL_ID) PARALLEL 16;
--將SELECT語(yǔ)句對(duì)應(yīng)STS進(jìn)行拆分為10份，每份22w條左右
DECLARE
L_CURR_TABLE_TIPS NUMBER :=0;
BEGIN
--SELECT 0..9尾號(hào) 改為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對(duì)應(yīng)數(shù)量
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）生成批量創(chuàng)建和刪除并行SQL Set Table的語(yǔ)句
用SPA用戶執(zhí)行,開(kāi)多個(gè)窗口，每個(gè)要10分鐘
--共13個(gè)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）檢查數(shù)量
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
可以并行執(zhí)行5個(gè)，多了會(huì)報(bào)ORA-01555錯(cuò)誤?？梢詫?xiě)成SHELL腳本放后臺(tái)執(zhí)行。
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分析需要的統(tǒng)計(jì)信息導(dǎo)入
測(cè)試環(huán)境中，準(zhǔn)確的統(tǒng)計(jì)信息是運(yùn)行SPA測(cè)試的基礎(chǔ)。先確定統(tǒng)計(jì)信息方案，等確定方案后，導(dǎo)入10g統(tǒng)計(jì)信息到11g,導(dǎo)入后可能還需要做一些處理，比如更新統(tǒng)計(jì)信息等。實(shí)際上，在SPA分析過(guò)程中，因?yàn)榻y(tǒng)計(jì)信息問(wèn)題導(dǎo)致2次SPA分析迭代，第1次：直接導(dǎo)入10g統(tǒng)計(jì)信息，由于發(fā)現(xiàn)大量表統(tǒng)計(jì)信息有問(wèn)題，缺失列信息，統(tǒng)計(jì)信息過(guò)舊等。后面確認(rèn)采用導(dǎo)入10g統(tǒng)計(jì)信息后重新收集：
生產(chǎn)庫(kù)未收集統(tǒng)計(jì)信息的表，除非出現(xiàn)大的性能問(wèn)題，確認(rèn)后收集。
生產(chǎn)庫(kù)收集了統(tǒng)計(jì)信息的表，如果統(tǒng)計(jì)信息不完整，比如列或索引等缺失，則收集。采用method_opt => FOR ALL COLUMNS SIZE REPEAT，可以保證原先沒(méi)有直方圖的采用for all columns size 1,有直方圖的更新直方圖，estimate_percent默認(rèn)。
統(tǒng)計(jì)信息處理流程如下：
將生產(chǎn)中需要的業(yè)務(wù)SCHEMA對(duì)象統(tǒng)計(jì)信息導(dǎo)出，然后傳輸?shù)綔y(cè)試環(huán)境中，注意傳到測(cè)試環(huán)境中，首先需要?jiǎng)h除原有的統(tǒng)計(jì)信息，否則可能出現(xiàn)不一致的問(wèn)題。流程和腳本如下：
1）從10g生產(chǎn)環(huán)境導(dǎo)出統(tǒng)計(jì)信息
--ogg.ogg_userlist存放需要導(dǎo)出的業(yè)務(wù)用戶名，用來(lái)拼導(dǎo)出腳本
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庫(kù) 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`
--后臺(tái)執(zhí)行
nohup ./exportstat.sh > exportstat.sh.log 2>&1 &
2）刪除11g測(cè)試環(huán)境統(tǒng)計(jì)信息
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）導(dǎo)入10g統(tǒng)計(jì)信息到11g測(cè)試環(huán)境中
-- 升級(jí)10g統(tǒng)計(jì)信息為11g,a,b庫(kù)都做,表結(jié)構(gòu)不同
exec DBMS_STATS.UPGRADE_STAT_TABLE(ownname=>SPA, stattab=>STAT_SNC_10G_20140916);
--導(dǎo)入，可編寫(xiě)shell腳本后臺(tái)執(zhí)行
--a庫(kù)
--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);
在最終升級(jí)的統(tǒng)計(jì)信息方案是導(dǎo)入10g統(tǒng)計(jì)信息到11g后，對(duì)10g未收集的不收集，收集的采用for all columns size repeat更新，詳細(xì)參考“最終升級(jí)統(tǒng)計(jì)信息方案”。
4.4 創(chuàng)建并行分析任務(wù)生成報(bào)告
通過(guò)對(duì)STS進(jìn)行拆分，可以實(shí)現(xiàn)SPA并行分析，SPA分析主要有：SPA任務(wù)創(chuàng)建、生成10g Trail,生成11g Trail,生成對(duì)比分析報(bào)告。
1）創(chuàng)建并行SPA分析任務(wù)
此步驟很快，小于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;
/
--查詢?nèi)蝿?wù)是否創(chuàng)建
select owner,task_name from DBA_ADVISOR_TASKS where task_name like SPA_TASK_RUN_%;
2）生成10g Trail
由于10g SQL執(zhí)行信息已經(jīng)采集到，所以此過(guò)程很快，小于10s，使用shell并行執(zhí)行。主要參數(shù)用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
--查詢執(zhí)行情況以及是否有錯(cuò)誤
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
并行測(cè)試生成11g的執(zhí)行信息,生成shell腳本，后臺(tái)執(zhí)行。這個(gè)過(guò)程最為關(guān)鍵的，也是最慢的過(guò)程，ORACLE會(huì)實(shí)際執(zhí)行STS中對(duì)應(yīng)SQL，有可能有的SQL執(zhí)行計(jì)劃改變，會(huì)執(zhí)行的很慢，從而影響整理過(guò)程。最容易出問(wèn)題的步驟就在這個(gè)過(guò)程中，因此，需要在執(zhí)行過(guò)程中進(jìn)行監(jiān)控和分析、甚至需要迭代重跑并行分析任務(wù)。主要參數(shù)用EXECUTION_TYPE指定為T(mén)EST 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分析任務(wù)進(jìn)度和狀態(tài)
由于第3步：生成11g Trail是非常耗時(shí)（庫(kù)如果不進(jìn)行STS拆分，需耗時(shí)13天左右）也是容易出問(wèn)題的步驟，因此，需要在工作日，隔一小時(shí)查看下進(jìn)度，并查看是否有報(bào)錯(cuò)。
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;
----日志查詢，查看出錯(cuò)信息
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分析報(bào)告
通過(guò)對(duì)比10g和11g SQL執(zhí)行統(tǒng)計(jì)信息:buffer gets、cpu time、elapsed time、plan_hash_value等來(lái)獲得執(zhí)行計(jì)劃是否改變、SQL性能是否下降報(bào)表。
并行執(zhí)行分析過(guò)程并產(chǎn)生報(bào)告（Shell環(huán)境中執(zhí)行，最好建立一個(gè)新的目錄spareport）,大約1小時(shí)。
cd /home/oracle/spa
mkdir spareport
腳本如下，放入后臺(tái)執(zhí)行
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分析的迭代過(guò)程
SPA分析經(jīng)過(guò)多次迭代，每迭代一次，則4.4的步驟就要重新做一遍，只需要修改對(duì)應(yīng)的TASK_NAME即可，如果找到正確的方法，可以減少迭代次數(shù)。每次迭代都是由于經(jīng)過(guò)SPA分析發(fā)現(xiàn)問(wèn)題，不得不修改如影響全局的優(yōu)化器參數(shù)、打開(kāi)或關(guān)閉fix control開(kāi)關(guān)、收集大量表的統(tǒng)計(jì)信息等，主要由于影響較大，所以需要重跑SPA分析。
第一次迭代：
設(shè)置參數(shù)_optimizer_squ_bottomup=true和_optimizer_cost_based_transformation=LINEAR。
第二次迭代：
將"_fix_control"增加9380298:ON，加上原有的開(kāi)關(guān)，執(zhí)行語(yǔ)句：
alter system set "_fix_control"=9380298:ON,8560951:ON,8893626:OFF,9344709:OFF,9195582:OFF;
第三次迭代：
導(dǎo)入10g統(tǒng)計(jì)信息到11g庫(kù)中后，對(duì)10g原先不收集的表還是不收集，已收集的表通過(guò)method_opt=>’for all columns size repeat’更新。
其它迭代：
由于執(zhí)行過(guò)程中報(bào)ORA-01555錯(cuò)誤。見(jiàn)4.6.SPA執(zhí)行分析過(guò)程注意點(diǎn)。
4.6 SPA執(zhí)行分析過(guò)程注意點(diǎn)
SPA執(zhí)行過(guò)程中，某些SQL可能因?yàn)閳?zhí)行計(jì)劃改變或者數(shù)據(jù)量變化，導(dǎo)致執(zhí)行超時(shí)或報(bào)錯(cuò)（ORA-01555等），這時(shí)，SPA分析可能終止，需要找出對(duì)應(yīng)SQL，從STS中清除出去，多帶帶分析或設(shè)置超時(shí)。如下：
查詢超時(shí)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%;
--導(dǎo)致ora-01555錯(cuò)誤的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;
報(bào)ORA-01555錯(cuò)誤，除了undo設(shè)置以外，還可能是執(zhí)行時(shí)間超長(zhǎng)，可以對(duì)執(zhí)行TASK設(shè)置超時(shí)，當(dāng)某個(gè)SQL超出XX秒后，則自動(dòng)結(jié)束，這個(gè)步驟，需要在生成11g Trial之前做，如下：
--設(shè)置超時(shí)時(shí)間 很重要，有的特別長(zhǎng)的，超時(shí)跳過(guò)，防止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開(kāi)頭的系列視圖以及DBA/USER_ADVISOR開(kāi)頭的系列視圖，主要查詢語(yǔ)句如下：
STS常用查詢?nèi)缦拢?/strong>
--查詢sts中采集sql數(shù)目
select name,owner,to_char(last_modified,yyyy-mm-dd hh24:mi:ss) last_modify,statement_count cnt from dba_sqlset;
--查詢對(duì)應(yīng)sql以及執(zhí)行信息，如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獲取綁定變量字段信息，關(guān)注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) */
在實(shí)際根據(jù)SPA報(bào)告分析性能下降原因時(shí)，可以通過(guò)以上腳本查詢出SQL以及對(duì)應(yīng)綁定變量,這樣方便進(jìn)行性能分析，直接使用SPA報(bào)告中的SQL，經(jīng)常因?yàn)榭崭竦仍驎?huì)報(bào)錯(cuò)。
SPA分析常用查詢?nèi)缦拢?/strong>
--檢查并行運(yùn)行的SPA任務(wù)的狀態(tài)
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;
--日志查詢，查看出錯(cuò)信息
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;
--查詢出錯(cuò)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 如何分析報(bào)告
根據(jù)每個(gè)STS跑出的SPA分析報(bào)告：buffer gets、cpu time、elapsed time、error、unsupport ，分別分析，重點(diǎn)關(guān)注buffer gets、cpu time、elapsed time 3份報(bào)告，按照順序分析，一般來(lái)說(shuō)，cpu time,elapsed time中出現(xiàn)的SQL，基本都在buffer gets中。報(bào)告樣式如下：
標(biāo)題頭：關(guān)注status 是否是COMPLETED,關(guān)注其他是否正常，比如SQL語(yǔ)句數(shù)目，出錯(cuò)信息對(duì)應(yīng)語(yǔ)句可以用SPA常見(jiàn)查詢，查詢出錯(cuò)SQL_ID。
匯總信息：查看SPA分析的匯總情況，總量，多少性能提高的，多少計(jì)劃改變的，未變的，出錯(cuò)的數(shù)量
明細(xì)信息：SPA列出TOP 300的信息，重點(diǎn)關(guān)注。有object_id,sql_id,執(zhí)行負(fù)載，10g執(zhí)行頻率，執(zhí)行前對(duì)應(yīng)指標(biāo)度量信息，執(zhí)行后對(duì)應(yīng)指標(biāo)度量信息，影響，計(jì)劃是否改變。
6.2 需要分析的SQL
對(duì)每份報(bào)告，首先看報(bào)告頭，判斷報(bào)告是否正常執(zhí)行完畢，如果正常，主要分析明細(xì)信息。將TOP 300的明細(xì)COPY到EXCEL中。然后按照plan change,選擇y的，然后按照Impact on SQL從小到大排序，只關(guān)注Impact on SQL值<0的。也就是分析執(zhí)行計(jì)劃改變、性能下降的SQL，由于升級(jí)伴隨著導(dǎo)入導(dǎo)出，一般執(zhí)行計(jì)劃未變的，無(wú)須分析，除非發(fā)現(xiàn)特別慢的，可以分析。最后excel可以增加備注列，說(shuō)明性能下降原因，以便匯總和解決。
以上excel每個(gè)報(bào)告一份，并且將buffer_gets、cpu time、elapsed time作為多帶帶的sheet。每個(gè)報(bào)告需要輸出：
6.3 分析性能下降原因
10.2.0.4升級(jí)到11.2.0.4,SQL出現(xiàn)性能下降，側(cè)重于分析如下方面：
優(yōu)化器新特性引入導(dǎo)致的BUG，如Adaptive Cursor sharing/Cardnality feedback，經(jīng)常存在導(dǎo)致SQL計(jì)劃頻繁改變。
優(yōu)化器新特性引入導(dǎo)致的限制，特別是查詢轉(zhuǎn)換方面的，如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謂詞推入
參數(shù)問(wèn)題，比如改變參數(shù)的默認(rèn)值，導(dǎo)致在10g中SQL性能很好，但是在11g中不可以。
優(yōu)化器改變導(dǎo)致執(zhí)行計(jì)劃細(xì)微差別，但是本質(zhì)一致，比如10g是UNION ALL PARTITION,11g是UNION ALL。
統(tǒng)計(jì)信息問(wèn)題：包括原10g統(tǒng)計(jì)信息不準(zhǔn)確，因?yàn)樗惴ú顒e，到11g中有問(wèn)題，解決需要重新收集，比如更新過(guò)期統(tǒng)計(jì)信息、不完整統(tǒng)計(jì)信息、需要收集某些列直方圖等。
對(duì)象有效性問(wèn)題：比如11g中索引因某些問(wèn)題導(dǎo)致失效，需要進(jìn)行索引有效性檢查。
FIX CONTROL開(kāi)關(guān)問(wèn)題，引入的很多特性都可以通過(guò)FIX CONTROL開(kāi)關(guān)控制。如"_fix_control"可以設(shè)置為9380298:ON。
SPA報(bào)告不準(zhǔn)確，可能數(shù)據(jù)量變化、或者返回行不同等會(huì)造成結(jié)果不同，但是SPA報(bào)告只對(duì)比對(duì)應(yīng)指標(biāo)性能，而且10g的指標(biāo)是個(gè)平均值,11g是單次SPA測(cè)試結(jié)果。
更多精彩干貨分享
點(diǎn)擊下方名片關(guān)注
IT那活兒
