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

主题：关于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; /