症状
升级DB到12.2.0.1 版本之后，SYSAUX 表空间增长过快并且消耗了大量的空间。
查询视图 VSYSAUX_OCCUPANTS 显示 SM/ADVISOR 占用空间排名靠前.
进一步查询 DBA_SEGMENTS 显示 WRI_ADV_OBJECTS 对象消耗了 SYSAUX 大量空间
例如：
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_IDX_01 SYS SYSAUX 1400 INDEX
…
更改
升级数据库到12.2.0.1.
原因
这个是由于12.2版本的一个新特性，即优化器统计信息顾问每天在维护窗口期间自动运行，因而引发了该问题。
AUTO_STATS_ADVISOR_TASK 任务运行了很多次导致SYSAUX表空间增长迅速。
例如:
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
…
解决方案
- 这个自动运行的任务(AUTO_STATS_ADVISOR_TASK) 可以被删除进而释放它占用的大量空间。
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
如果上述报错出现，可以用下面的方法重建AUTO_STATS_ADVISOR_TASK来解决：
SQL> connect / as sysdba
SQL> EXEC DBMS_STATS.INIT_PACKAGE();
删除任务之后，重组表和所有索引：
SQL> ALTER TABLE WRI_ADV_OBJECTS_IDX_01 REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
2.如果表WRI$_ADV_OBJECTS中有太多和Auto Stats Advisor Task相关的记录，删除任务时可能会需要大量undo空间.
一旦发生此状况，如下方法可以用来直接清理数据而避免产生大量redo 和undo信息。
检查表 WRI_ADV_OBJECTS和 Auto Stats Advisor Task相关的有效条数 SQL> SELECT COUNT(*) FROM WRI_ADV_OBJECTS WHERE TASK_ID=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME=‘AUTO_STATS_ADVISOR_TASK’);
创建新表并保留非 AUTO_STATS_ADVISOR_TASK数据
SQL> CREATE TABLE WRI_ADV_OBJECTS WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI_ADV_OBJECTS_NEW;
截断原表
SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;
把备份表中数据插回原表
SQL> INSERT /*+ APPEND */ INTO WRI_ADV_OBJECTS_NEW;
SQL> COMMIT;
重建索引
SQL> ALTER INDEX WRI_ADV_OBJECTS_PK REBUILD;
P.S: 表 WRI$_ADV_OBJECTS_NEW在上述命令都成功执行后可删除。
从数据字典删除统计信息任务，避免再次造成问题。
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := ‘AUTO_STATS_ADVISOR_TASK’;
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
DBA可以在任何时间重建统计信息顾问任务 (AUTO_STATS_ADVISOR_TASK)，具体参考如下办法：
SQL> EXEC DBMS_STATS.INIT_PACKAGE();
运行如下命令，统计信息顾问任务 (AUTO_STATS_ADVISOR_TASK) 不会被DISABLE.
DECLARE
filter1 CLOB;
BEGIN
filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(‘AUTO_STATS_ADVISOR_TASK’,‘EXECUTE’,NULL,‘DISABLE’);
END;
/
更方便的方法来禁用这个任务（參考如下：）。
Unpublished Bug 26749785 NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK
