Oracle优化器统计信息顾问任务AUTO_STATS_ADVISOR_TASK
👉 本文共约731个字，系统预计阅读时间或需3分钟。
简介
生成建议
1 2 3 4 5 6 7 8 9 10 | SET LINESIZE 3000 SET LONG 500000 SET PAGESIZE 0 SET LONGCHUNKSIZE 100000 SELECT DBMS_STATS.REPORT_ADVISOR_TASK('AUTO_STATS_ADVISOR_TASK',NULL,'TEXT','ALL','ALL') AS REPORT FROM DUAL; SELECT * from DBA_ADVISOR_RECOMMENDATIONS d where d.task_name='AUTO_STATS_ADVISOR_TASK' ORDER by rec_id desc ; |
SM/ADVISOR 占用空间排名
查询视图 V$SYSAUX_OCCUPANTS 显示 SM/ADVISOR 占用空间排名靠前.
这个是由于12.2版本的一个新特性，即优化器统计信息顾问每天在维护窗口期间自动运行，因而引发了该问题。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> SET LINES 120 SQL> COL OCCUPANT_NAME FORMAT A30 SQL> SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC; OCCUPANT_NAME SPACE_USAGE_KBYTES ------------------------------ ------------------ SM/ADVISOR 5901376 SM/OPTSTAT 574080 ... SQL> COL SEGMENT_NAME FORMAT A30 SQL> COL OWNER FORMAT A10 SQL> COL TABLESPACE_NAME FORMAT A10 SQL> COL SEGMENT_TYPE FORMAT A15 SQL> SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10; SEGMENT_NAME OWNER TABLESPACE SIZE(MB) SEGMENT_TYPE ------------------------------ ---------- ---------- ---------- --------------- WRI$_ADV_OBJECTS SYS SYSAUX 3600 TABLE WRI$_ADV_OBJECTS_IDX_01 SYS SYSAUX 1400 INDEX ... |
AUTO_STATS_ADVISOR_TASK 任务运行了很多次导致SYSAUX表空间增长迅速。
1 2 3 4 5 6 7 | SQL> COL TASK_NAME FORMAT A35 SQL> SELECT TASK_NAME, COUNT(*) CNT FROM DBA_ADVISOR_OBJECTS GROUP BY TASK_NAME ORDER BY CNT DESC; TASK_NAME CNT ----------------------------------- ---------- AUTO_STATS_ADVISOR_TASK 27082431 SYS_AUTO_SPM_EVOLVE_TASK 19 SYS_AUTO_SQL_TUNING_TASK 39 |
从 12.2 开始，Statistics Advisor 任务执行了很多，并且在 SYSAUX 表空间中消耗了更多空间。
删除
如果不希望运行AUTO_STATS_ADVISOR_TASK。请参考下边的方法删除Statistics Advisor任务（AUTO_STATS_ADVISOR_TASK）。
1 2 3 4 5 6 7 | DECLARE v_tname VARCHAR2(32767); BEGIN v_tname := 'AUTO_STATS_ADVISOR_TASK'; DBMS_STATS.DROP_ADVISOR_TASK(v_tname); END; / |
一旦任务被删除，所有依赖Auto Stats Advisor Task的相关历史输出信息将从WRI$_ADV_OBJECTS表中一并删除。
删除任务AUTO_STATS_ADVISOR_TASK可能引发如下报错：
ORA-20001: Statistics Advisor: Invalid Task Name For the current user
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
如果上述报错出现，可以用下面的方法重建AUTO_STATS_ADVISOR_TASK来解决：
1 2 | SQL> connect / as sysdba SQL> EXEC DBMS_STATS.INIT_PACKAGE(); |
删除任务之后，重组表和所有索引：
1 2 3 | SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE; SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD; SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD; |
修改数据保留期限
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME IN 'EXECUTION_DAYS_TO_EXPIRE'; -- 可以通过修改保留时间，限制保留为 31 天： BEGIN DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER ( task_name => 'AUTO_STATS_ADVISOR_TASK' , parameter => 'EXECUTION_DAYS_TO_EXPIRE' , value => 31 ); END; / |
参考
升级DB到12.2.0.1版本之后，由于统计信息顾问导致SYSAUX 过快增长 (Doc ID 2440139.1)
发现AUTO_STATS_ADVISOR_TASK在维护窗口之外的时间里运行 (Doc ID 2668082.1)
从12.2开始如何关闭 Optimizer Statistics Advisor 功能 (Doc ID 2728765.1)
