【12.2 新功能】优化器统计顾问（Optimizer Statistics Advisor）及常见问题

Optimizer Statistics Advisor，统计信息 ，ORA-20001 ， ORA-00932 ，12.2.0.1

对于Oracle的CBO优化器而言，统计信息是其最重要的组成部分。
因此，Oracle在统计信息的准确性方面不断地进行优化和加强。
从12.2版本开始，Oracle推出了优化器统计顾问（Optimizer Statistics Advisor），
根据事先定义的规则，定期执行，提供给用户参考的统计信息收集相关的建议。

参考：

统计信息收集相关内容：

数据库自动维护任务介绍
数据库自动维护任务介绍（二）
自动统计信息收集Automatic Optimizer Statistics Collection
自动段指导Automatic Segment Advisor

在12.2.0.1以后的版本上运行该功能时，遇到问题的报告很多,但是Oracle也在不断地修正着相关地问题，所以保持数据库为最新版本通常是一个最佳的选择。

问题1：ORA-12012, ORA-20001等错误

由于12.2.0.1等版本上的设计不足(Bug) 等，可能会在告警日志（alert log）或者JOB跟踪日志中，发现ORA-12012, ORA-20001等错误。

例：

可以参考如下方法尝试解决。

1.通过dbms_stats.init_package()程序包，重新创建优化器统计顾问任务。

2. 首先看看这个bug 27983174和27774706是否被修复，如果存在则建议打上补丁或打上最新的RU.
   如果没有被修复，可向官方技术支持申请Patch 27983174 和Patch 27774706 补丁并应用。

3. 应用Patch 27774706 补丁后，执行如下操作：

4.如果应用Patch 27774706 补丁过程中出错的话，试着执行如下命令手动更新状态。

很多问题会在19c以后的版本进行修复，所以有可能的话注意保持版本的更新。

参考：

Recurring ORA-12012, ORA-20001, ORA-06512 In Container Database (Doc
ID 2420581.1) 12.2.0.0.2 Automatic Statistics Advisor Job Errors with
Statistics Fatal Error (Doc ID 2448436.1) Version 12.2.0.1 to
18.3.0.0.0 ORA-12012 Error on auto execute of job “SYS”."ORA$AT_OS_OPT_SY_ in 12.2.0 Database version or higher
release (like 18c) (Doc ID 2127675.1) Version 12.2.0.1 and later

问题2：SYSAUX表空间增长过快

由于优化器统计顾问的执行，SYSAUX表空间增长过快，导致可用表空间不足。

特征：
V$SYSAU{X}_{O}CCUPANTS中，SM/ADVISOR占据大量空间DB{A}_{S}EGMENTS中，WRI$_ADV_OBJECTS占据大量空间

解决方法：

1. 尝试删除Statistics Advisor 任务(AUTO_STATS_ADVISOR_TASK)，然后重建。

   具体删除方法例:

– 确认当前设定的保持期间
select task_name, parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS
where task_name=‘AUTO_STATS_ADVISOR_TASK’ and PARAMETER_NAME like ‘%EXPIRE%’;

参考：

How To Set DAYS_TO_EXPIRE and EXECUTION_DAYS_TO_EXPIRE of Automatic Statistics Advisor Task (Doc ID 2544788.1)

参考：

升级DB到12.2.0.1版本之后，由于统计信息顾问导致SYSAUX 过快增长 (Doc ID 2440139.1) SYSAUX
Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To
Statistics Advisor (Doc ID 2305512.1)

参考：

AUTO_STATS_ADVISOR_TASK Not Purging Even Though Setting EXECUTION_DAYS_TO_EXPIRE (Doc ID 2615851.1)

Bug 26764561(Doc ID 26764561.8)

■ 关于EXECUTION_DAYS_TO_EXPIRE的默认值：

问题3：关于在多租户环境中 CDB/PDB的设置问题

对于多租户环境中，CDB/PDB的设置相对独立，互相不影响。

■EXECUTION_DAYS_TO_EXPIRE的CDB/PDB设置测试：

■PDB/CDB的JOB关联性的测试：

SQL> conn / as sysdba
Connected.
SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in (‘AUTO_STATS_ADVISOR_TASK’,‘INDIVIDUAL_STATS_ADVISOR_TASK’); 2
AUTO_STATS_ADVISOR_TASK 25-SEP-19 CMD SYS

SQL> DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := ‘AUTO_STATS_ADVISOR_TASK’;
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.

SQL> select name, ctime, how_created,OWNER_NAME from sys.wri${}_{a}d{v}_{t}askswherenamein{(}^{\prime }AUT{O}_{S}TAT{S}_{A}DVISO{R}_{T}AS{K}^{\prime }{,}^{\prime }INDIVIDUA{L}_{S}TAT{S}_{A}DVISO{R}_{T}AS{K}^{\prime });2norowsselectedSQL>showpdbs2PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO

—PDB中JOB的状态
SQL> alter session set container=pdb1;
Session altered.

SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in (‘AUTO_STATS_ADVISOR_TASK’,‘INDIVIDUAL_STATS_ADVISOR_TASK’);
2 AUTO_STATS_ADVISOR_TASK 26-JAN-17 CMD SYS
INDIVIDUAL_STATS_ADVISOR_TASK 26-JAN-17 CMD SYS

SQL> DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := ‘AUTO_STATS_ADVISOR_TASK’;
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.

SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in (‘AUTO_STATS_ADVISOR_TASK’,‘INDIVIDUAL_STATS_ADVISOR_TASK’);
2 INDIVIDUAL_STATS_ADVISOR_TASK 26-JAN-17 CMD SYS

—CDB 不会影响PDB
SQL> conn / as sysdba
Connected.
SQL> EXEC DBMS_STATS.INIT_PACKAGE();
PL/SQL procedure successfully completed.

SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in (‘AUTO_STATS_ADVISOR_TASK’,‘INDIVIDUAL_STATS_ADVISOR_TASK’);
2 AUTO_STATS_ADVISOR_TASK 15-NOV-19 CMD SYS
INDIVIDUAL_STATS_ADVISOR_TASK 15-NOV-19 CMD SYS

SQL> alter session set container=pdb1;
Session altered.

SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in (‘AUTO_STATS_ADVISOR_TASK’,‘INDIVIDUAL_STATS_ADVISOR_TASK’);
2 INDIVIDUAL_STATS_ADVISOR_TASK 26-JAN-17 CMD SYS
SQL>

问题4：PGA 限制ORA-4036

在执行统计信息收集JOB等的过程中会调用优化器统计顾问任务。
如果Statistics Advisor 任务的数据过多，有可能导致在执行统计信息收集JOB过程中引发ORA-4036.
例如输出的跟踪日志中的HEAP DUMP中qosadvCreateSu Chunk占用了较大空间，qosadvCreateSu是Statistics Advisor运行时所需的heap。

可以通过定期删除Statistics Advisor 任务的数据 或者增加PGA_AGGREGATE_LIMIT的大小来解决。

专注于技术不限于技术！
用碎片化的时间，一点一滴地提高数据库技术和个人能力。
欢迎关注微信订阅号：Oracle数据库技术（TeacherWhat）