1.症状

升级DB到12.2.0.1 版本之后，SYSAUX 表空间增长过快并且消耗了大量的空间。
• 查询视图 V$SYSAUX_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 SYS SYSAUX 3600 TABLE
WRI$_ADV_OBJECTS_IDX_01 SYS SYSAUX 1400 INDEX

2.问题原因

这个是由于12.2版本的一个新特性，即优化器统计信息顾问每天在维护窗口期间自动运行，因而引发了该问题。AUTO_STATS_ADVISOR_TASK 任务运行了很多次导致SYSAUX表空间增长迅速。
例如：

-- 从 12.2 开始，Statistics Advisor 任务执行了很多，并且在 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
..

3.处理方法

方法1： 删除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表中一并删除。删除之后，请重组表WRI$_ADV_OBJECTS 和它相关的index。

SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD; /* this index is included from 19c and above */

确保所有索引都有效。

SQL> column index_name format a30
SQL> select index_name,status from dba_indexes where table_name='WRI$_ADV_OBJECTS' and index_type like
'%NORMAL';
INDEX_NAME STATUS
------------------------------ --------
WRI$_ADV_OBJECTS_PK VALID
WRI$_ADV_OBJECTS_IDX_01 VALID
WRI$_ADV_OBJECTS_IDX_02 VALID

对于 Multitenant/PDB 环境，重组表及其索引如下:

SQL> alter session set container=<PDB_NAME>;
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER TABLE WRI$_ADV_OBJECTS MOVE');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD'); /* This index
is available from 19c only */

方法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_NEW AS SELECT * FROM WRI$_ADV_OBJECTS WHERE TASK_ID !
=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');
SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS_NEW;
### 截断原表 ###
SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;
### 把备份表中数据插回原表 ###
SQL> INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;
For 19c & above, use the below insert statement to avoid ORA-54013 error as there is a new column SQL_ID_VC
added to WRI$_ADV_OBJECTS.
SQL> INSERT INTO WRI$_ADV_OBJECTS("ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2"
,"ATTR3" ,"ATTR4" ,"ATTR5" ,"ATTR6" ,"ATTR7" ,"ATTR8" ,"ATTR9"
,"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18","ATTR19","ATTR20","OTHER"
,"SPARE_N1" ,"SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3"
,"SPARE_C4" ) SELECT "ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2" ,"ATTR3" ,"ATTR4"
,"ATTR5" ,"ATTR6" ,"ATTR7" ,"ATTR8" ,"ATTR9" ,
"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18","ATTR19","ATTR20","OTHER"
,"SPARE_N1" , "SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3"
,"SPARE_C4" FROM WRI$_ADV_OBJECTS_NEW;
SQL> COMMIT;
### 重建索引 ###
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
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;
/