阿里云-云小站（无限量代金券发放中）
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

231次阅读
没有评论

共计 14007 个字符，预计需要花费 36 分钟才能阅读完成。

主题：关于 Oracle 开启自动收集统计信息的 SPA 测试
环境：Oracle RAC 11.2.0.4（Primary + Standby）
需求：生产 Primary 库由于历史原因关闭了自动统计信息的收集，目前客户需求是想要重新开启统计信息的自动收集，虽然一般来说，有了更准确的统计信息，SQL 会有更好的执行计划，但由于生产环境数据复杂，实际上还是需要评估哪些 SQL 会因为重新开启自动统计信息收集性能反而会下降。
方案：本着尽可能减少对生产 Primary 环境影响的原则，在 Standby DG 环境临时开启 snapshot standby 来进行 SPA（SQL Performance Analyze）测试，比对开启统计信息自动收集前后的性能差异，给客户提供有价值的参考。

  • 1. 构造测试环境
  • 2.DG 备库开启 snapshot 模式
  • 3.SPA 测试准备
  • 4. 从 AWR 中采集 SQL
  • 5.SPA 分析比较
  • 6. 获取性能比对分析报告

1. 构造测试环境

检查自动统计信息的开启状态：
select client_name,status from dba_autotask_client;
确认自动统计信息的收集是关闭的，对于“auto optimizer stats collection”的状态应该是“DISABLED”。

SQL> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  DISABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED

附：关闭数据库的自动统计信息收集：

-- 光闭自动统计信息收集，（慎用，除非有其他手工收集统计信息的完整方案，否则不建议关闭）
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/

DG 备库保持和主库同步，所以这些设置项也都是完全一样的。

2.DG 备库开启 snapshot 模式

主要就是在 mount 模式下切换数据到 snapshot Standby 模式再 read write 打开库，为之后测试做准备。下面是核心步骤：

SQL> shutdown immediate
SQL> startup mount
SQL> alter database convert to snapshot standby;
SQL> shutdown immediate
SQL> startup

关于其他细节可参考下面文章，主要是为“开启 11gR2 DG 的快照模式”，“后续还原成备库”等操作提供参考：

  • ORACLE 11gR2 DG(Physical Standby)日常维护 02

3.SPA 测试准备

进行 SPA 测试时，强烈建议在数据库中创建 SPA 测试专用用户，这样可以与其他用户区分开以及避免误操作。

SQL>
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;

4. 从 AWR 中采集 SQL

备库从 AWR 中采集到 SQL。
4.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;

我这里的结果是：

    MIN_ID     MAX_ID
---------- ----------
      2755       2848

4.2 新建 SQL Set
注意：以下的规范部分都是引用之前同事编写的 SPA 操作规范。

参考规范：

EXEC DBMS_SQLTUNE.DROP_SQLSET ( -
                  SQLSET_NAME  => '${DBNAME}_SQLSET_${YYYYMMDD}',
                  SQLSET_OWNER => 'SPA');

EXEC DBMS_SQLTUNE.CREATE_SQLSET ( -
                  SQLSET_NAME  => '${DBNAME}_SQLSET_${YYYYMMDD}', -
                  DESCRIPTION  => 'SQL Set Create at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), -
                  SQLSET_OWNER => 'SPA');

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

-- 连接用户
conn SPA/SPA

-- 如果之前有这个 SQLSET 的名字，可以这样删除
EXEC DBMS_SQLTUNE.DROP_SQLSET (SQLSET_NAME  => 'JYZHAO_SQLSET_20180106', SQLSET_OWNER => 'SPA');

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

4.3 转化 AWR 数据中的 SQL，将其载入到 SQL Set
从备库的 AWR 中提取 SQL（这等同于主库历史的 SQL）。
参考规范：

DECLARE
  SQLSET_CUR DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN SQLSET_CUR FOR
    SELECT VALUE(P) FROM TABLE(
           DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( &MINID, &MAXID,
                        'PARSING_SCHEMA_NAME NOT IN (''SYS'',''SYSTEM'')',
                        NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P;
  DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME => '${DBNAME}_SQLSET_${YYYYMMDD}',
               SQLSET_OWNER => 'SPA',
               POPULATE_CURSOR => SQLSET_CUR,
               LOAD_OPTION => 'MERGE',
               UPDATE_OPTION => 'ACCUMULATE');
  CLOSE SQLSET_CUR;
    END;
/

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

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

4.4 打包 SQL Set(可不做)
参考规范：

DROP TABLE SPA.${DBNAME}_SQLSETTAB_${YYYYMMDD};
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET ('${DBNAME}_SQLSETTAB_${YYYYMMDD}',‘SPA’, 'SYSAUX');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET ( -
                  SQLSET_NAME          => '${DBNAME}_SQLSET_${YYYYMMDD}', -
                  SQLSET_OWNER         =>‘SPA’, -
                  STAGING_TABLE_NAME   => '${DBNAME}_SQLSETTAB_${YYYYMMDD}', -
                  STAGING_SCHEMA_OWNER =>‘SPA’);

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

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

说明：其实在我这里的测试场景下，这一步是不需要做的。因为备库的 SQL Set 可以直接在后面引用，不需要像 SPA 经典场景中，是从生产源环境打包导出来后，在测试环境再导入进去，再解包为 SQL Set。

5.SPA 分析比较

5.1 创建 SPA 分析任务
参考规范：

VARIABLE SPA_TASK  VARCHAR2(64);
EXEC :SPA_TASK := DBMS_SQLPA.CREATE_ANALYSIS_TASK(  -
                             TASK_NAME    => 'SPA_TASK_${YYYYMMDD}', -
                             DESCRIPTION => 'SPA Analysis task at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), -
                             SQLSET_NAME  => '${DBNAME}_SQLSET_${YYYYMMDD}', -
                             SQLSET_OWNER =>‘SPA’);

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

-- 创建 SPA 分析任务：VARIABLE SPA_TASK  VARCHAR2(64);
EXEC :SPA_TASK := DBMS_SQLPA.CREATE_ANALYSIS_TASK(  -
                             TASK_NAME    => 'SPA_TASK_20180106', -
                             DESCRIPTION => 'SPA Analysis task at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), -
                             SQLSET_NAME  => 'JYZHAO_SQLSET_20180106', -
                             SQLSET_OWNER => 'SPA');

5.2 获取变更前的 SQL 执行效率
参考规范：

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
                TASK_NAME      => 'SPA_TASK_${YYYYMMDD}', -
                EXECUTION_NAME => 'EXEC_10G_${YYYYMMDD}', -
                EXECUTION_TYPE => 'CONVERT SQLSET', -
                EXECUTION_DESC => 'Convert 10g SQLSET 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_BEFORE_20180106', -
                EXECUTION_TYPE => 'CONVERT SQLSET', -
                EXECUTION_DESC => 'Convert Before gathering stats SQLSET for SPA Task at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

5.3 开启变更操作
变更内容：开启统计信息自动收集并确认已经成功收集了最新的统计信息。
这里首先需要开启统计信息自动收集，并可以把自动收集的窗口时间提前到现在，减少等待的时间。

-- 检查自动统计信息的开启状态：
select client_name,status from dba_autotask_client;

-- 启动自动统计信息收集
BEGIN
  DBMS_AUTO_TASK_ADMIN.enable(client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/

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

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. 获取性能比对分析报告

参考规范：

--a)  获取执行时间全部报告
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_${YYYYMMDD}','HTML','ALL','ALL',NULL,1000,'SPA_TASK_${YYYYMMDD}_COMP_ET')).GETCLOBVAL(0,0) FROM DUAL;
--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_${YYYYMMDD}','HTML','REGRESSED','ALL',NULL,1000,'SPA_TASK_${YYYYMMDD}_COMP_ET')).GETCLOBVAL(0,0) FROM DUAL;
--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_${YYYYMMDD}','HTML','ALL','ALL',NULL,1000,'SPA_TASK_${YYYYMMDD}_COMP_BG')).GETCLOBVAL(0,0) FROM DUAL;
--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_${YYYYMMDD}','HTML','REGRESSED','ALL',NULL,1000,'SPA_TASK_${YYYYMMDD}_COMP_BG')).GETCLOBVAL(0,0) FROM DUAL;
--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_${YYYYMMDD}','HTML','ERRORS','ALL',NULL,1000,'SPA_TASK_${YYYYMMDD}_COMP_ET')).GETCLOBVAL(0,0) FROM DUAL;
--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_${YYYYMMDD}','HTML','UNSUPPORTED','ALL',NULL,1000,'SPA_TASK_${YYYYMMDD}_COMP_ET')).GETCLOBVAL(0,0) FROM DUAL;
--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_${YYYYMMDD}','HTML','CHANGED_PLANS','ALL',NULL,1000,'SPA_TASK_${YYYYMMDD}_COMP_ET')).GETCLOBVAL(0,0) FROM DUAL;

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

--a)  获取执行时间全部报告
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_20180106','HTML','ALL','ALL',NULL,1000,'COMPARE_ET_20180106')).GETCLOBVAL(0,0) FROM DUAL;
--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_20180106','HTML','REGRESSED','ALL',NULL,1000,'COMPARE_ET_20180106')).GETCLOBVAL(0,0) FROM DUAL;
--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_20180106','HTML','ALL','ALL',NULL,1000,'COMPARE_BG_20180106')).GETCLOBVAL(0,0) FROM DUAL;
--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_20180106','HTML','REGRESSED','ALL',NULL,1000,'COMPARE_BG_20180106')).GETCLOBVAL(0,0) FROM DUAL;
--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_20180106','HTML','ERRORS','ALL',NULL,1000,'COMPARE_ET_20180106')).GETCLOBVAL(0,0) FROM DUAL;
--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_20180106','HTML','UNSUPPORTED','ALL',NULL,1000,'COMPARE_ET_20180106')).GETCLOBVAL(0,0) FROM DUAL;
--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_20180106','HTML','CHANGED_PLANS','ALL',NULL,1000,'COMPARE_ET_20180106')).GETCLOBVAL(0,0) FROM DUAL;

这样就得到了各类的性能对比报告，以执行时间的全部报告为例，生成的报告概要头部类似这样：
关于 Oracle 开启自动收集统计信息的 SPA 测试
当然，具体获取到的这些性能对比报告，针对那些有性能下降的 SQL，还需要人工干预，评估如何优化处理那些性能下降的 SQL。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2018-01/150160.htm

正文完
星哥玩云-微信公众号
post-qrcode
MongoDB MySQL NoSQL Oracle Oracle SPA Oracle教程 Oracle数据库 Oracle自动收集统计信息 PostgreSQL 关于Oracle开启自动收集统计信息的SPA测试 数据库
发表至： 数据库技术
2022-01-22
 0
星锅
版权声明：本站原创文章，由 星锅 于2022-01-22发表，共计14007字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
【腾讯云】推广者专属福利，新客户无门槛领取总价值高达2860元代金券，每种代金券限量500张，先到先得。
MongoDB用户权限控制
MySQL事务表和非事务表
Oracle权限的分配与回收
MySQL的索引详解
Percona Xtrabackup 安装
借助OGG完成Oracle到MySQL的数据迁移
Oracle修改字符集ORA-02374，ORA-12899，ORA-02372
MySQL 5.6.38优化实例一则
阿里云-最新活动爆款每日限量供应
评论（没有评论）
验证码
星哥说事
星哥说事
分享互联网知识
阅读量
3478337
评论数
4
文章搜索
热门文章
获取各大人工智能AI工具通过API和KEY调用的方法

获取各大人工智能AI工具通过API和KEY调用的方法

获取各大人工智能 AI 工具通过 API 和 KEY 调用的方法 前言 随着人工智能技术的飞速发展，AI 工具...
安装开源软件ChatALL（齐叨）来聚合各大人工智能工具

安装开源软件ChatALL（齐叨）来聚合各大人工智能工具

安装开源软件 ChatALL（齐叨）来聚合各大人工智能工具 前言 如果你跟我一样无论遇到什么问题都要询问人工智...
给你的NAS无限可能，安装小晓雅全家桶影音库

给你的NAS无限可能，安装小晓雅全家桶影音库

给你的 NAS 无限可能，安装小晓雅全家桶影音库 家庭存储和娱乐的需求日益增长。无论是高清电影、电视剧，还是音...
vmware下的网卡分配问题

vmware下的网卡分配问题

导读 vmware 虚拟机下 linux centos6.6 只有 lo，没有 eth0 网卡、随机分配 ip...
软件开发之递归操作

软件开发之递归操作

导读 递归是很神奇的，但是在大多数的编程类书藉中对递归讲解的并不好。它们只是给你展示一个递归阶乘的实现，然后警...
CDN
阿里云CDN-提高用户访问的响应速度和成功率
随机文章
Windows和Linux设计和原理哪个系统更先进呢？

Windows和Linux设计和原理哪个系统更先进呢？

导读 作为一个写了多年 C ++/VC++ 代码，在 Windows 和 Linux 平台都做过开发的程序员，...
搭建自己的yum仓库

搭建自己的yum仓库

导读 yum（Yellow dog Updater, Modified）是一个在 Fedora 和 RedHa...
Linux之rar文件解压之路

Linux之rar文件解压之路

导读 相信大家在使用 Linux 系统时有时候会碰到有关于 rar 文件的解压缩。网上下载 rar 压缩文件的...
如何解决MySQL内存不足启动失败的问题

如何解决MySQL内存不足启动失败的问题

1. 启动 MySQL 时一直不成功，查看错误日志 /var/log/mysql/error.log 2. 主...
MySQL 8更新了什么？

MySQL 8更新了什么？

MySQL 的第八个版本蓄势待发，并有望于 2018 年发布。在 MySQL 5.7.9 的首个通用版本推出后...
一言一句话
-「」
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中