关于Oracle开启自动收集统计信息的SPA测试(3)

查看窗口任务和有关统计信息自动收集的任务执行状态：

select window_name,repeat_interval,duration,enabled from dba_scheduler_windows; select owner, job_name, status, ACTUAL_START_DATE, RUN_DURATION from dba_scheduler_job_run_details where job_name like 'ORA$AT_OS_OPT_S%' order by 4;

调整窗口任务的下一次执行时间：

--需要确认JOB可以启动 alter system set job_queue_processes=1000; --调整窗口任务的下一次执行时间 EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW','repeat_interval','freq=daily;byday=SAT;byhour=17;byminute=10;bysecond=0');

更多有关调整窗口和自动任务的内容可参考文章：

Oracle的窗口和自动任务

5.4 变更后再次分析性能
测试运行SQL Tuning Set中的SQL语句，分析所有语句在收集统计信息之后的执行效率：
参考规范：

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( - TASK_NAME => 'SPA_TASK_${YYYYMMDD}', - EXECUTION_NAME => 'EXEC_11G_${YYYYMMDD}', - EXECUTION_TYPE => 'TEST EXECUTE', - EXECUTION_DESC => 'Execute SQL in 11g for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

依据我的实验环境，真实的示例为：

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( - TASK_NAME => 'SPA_TASK_20180106', - EXECUTION_NAME => 'EXEC_AFTER_20180106', - EXECUTION_TYPE => 'TEST EXECUTE', - EXECUTION_DESC => 'Execute SQL After gathering stats for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

5.5 变更前后性能对比
得到两次SQL Trail之后，可以对比两次Trial之间的SQL执行性能，可以从不同的维度对两次Trail中的所有SQL进行对比分析，主要关注的维度有：SQL执行时间，SQL执行的CPU时间，SQL执行的逻辑读。
参考规范：

1). 对比两次Trail中的SQL执行时间 EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( - TASK_NAME => 'SPA_TASK_${YYYYMMDD}', - EXECUTION_NAME => 'COMPARE_ET_${YYYYMMDD}', - EXECUTION_TYPE => 'COMPARE PERFORMANCE', - EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( - 'COMPARISON_METRIC', 'ELAPSED_TIME', - 'EXECUTE_FULLDML', 'TRUE', - 'EXECUTION_NAME1','EXEC_10G_${YYYYMMDD}', - 'EXECUTION_NAME2','EXEC_11G_${YYYYMMDD}'), - 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_${YYYYMMDD}', - EXECUTION_NAME => 'COMPARE_CT_${YYYYMMDD}', - EXECUTION_TYPE => 'COMPARE PERFORMANCE', - EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( - 'COMPARISON_METRIC', 'CPU_TIME', - 'EXECUTION_NAME1','EXEC_10G_${YYYYMMDD}', - 'EXECUTION_NAME2','EXEC_11G_${YYYYMMDD}'), - 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_D', - EXECUTION_NAME => 'COMPARE_BG_D', - EXECUTION_TYPE => 'COMPARE PERFORMANCE', - EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( - 'COMPARISON_METRIC', 'BUFFER_GETS', - 'EXECUTION_NAME1','EXEC_10G_${YYYYMMDD}', - 'EXECUTION_NAME2','EXEC_11G_${YYYYMMDD}'), - EXECUTION_DESC => 'Compare SQLs between 10g and 11g at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

依据我的实验环境，真实的示例为：

1). 对比两次Trail中的SQL执行时间 EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( - TASK_NAME => 'SPA_TASK_20180106', - EXECUTION_NAME => 'COMPARE_ET_20180106', - EXECUTION_TYPE => 'COMPARE PERFORMANCE', - EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( - 'COMPARISON_METRIC', 'ELAPSED_TIME', - 'EXECUTE_FULLDML', 'TRUE', - 'EXECUTION_NAME1','EXEC_BEFORE_20180106', - 'EXECUTION_NAME2','EXEC_AFTER_20180106'), - 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_20180106', - EXECUTION_NAME => 'COMPARE_CT_20180106}', - EXECUTION_TYPE => 'COMPARE PERFORMANCE', - EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( - 'COMPARISON_METRIC', 'CPU_TIME', - 'EXECUTION_NAME1','EXEC_BEFORE_20180106', - 'EXECUTION_NAME2','EXEC_AFTER_20180106'), - 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_20180106', - EXECUTION_NAME => 'COMPARE_BG_20180106', - EXECUTION_TYPE => 'COMPARE PERFORMANCE', - EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( - 'COMPARISON_METRIC', 'BUFFER_GETS', - 'EXECUTION_NAME1','EXEC_BEFORE_20180106', - 'EXECUTION_NAME2','EXEC_AFTER_20180106'), - EXECUTION_DESC => 'Compare SQLs between Before_STATS and After_STATS at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')); 6.获取性能比对分析报告