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测试结果。
