oracle wri$_adv_objects突增,升级DB到12.2.0.1版本之后，由于统计信息顾问导致SYSAUX 过快增长...

症状

升级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

WRIADVOBJECTSSYSSYSAUX3600TABLEWRI_ADV_OBJECTS SYS SYSAUX 3600 TABLE

WRIA​DVO​BJECTSSYSSYSAUX3600TABLEWRI_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 WRIADVOBJECTSMOVE;SQL>ALTERINDEXWRI_ADV_OBJECTS MOVE;

SQL> ALTER INDEX WRIA​DVO​BJECTSMOVE;SQL>ALTERINDEXWRI_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 WRIADVOBJECTSNEWASSELECT∗FROMWRI_ADV_OBJECTS_NEW AS SELECT * FROM WRIA​DVO​BJECTSN​EWASSELECT∗FROMWRI_ADV_OBJECTS WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRIADVTASKSWHERENAME=′AUTOSTATSADVISORTASK′);SQL>SELECTCOUNT(∗)FROMWRI_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

SQL> SELECT COUNT(*) FROM WRIA​DVT​ASKSWHERENAME=′AUTOS​TATSA​DVISORT​ASK′);SQL>SELECTCOUNT(∗)FROMWRI_ADV_OBJECTS_NEW;

截断原表

SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;

把备份表中数据插回原表

SQL> INSERT /*+ APPEND */ INTO WRIADVOBJECTSSELECT∗FROMWRI_ADV_OBJECTS SELECT * FROM WRIA​DVO​BJECTSSELECT∗FROMWRI_ADV_OBJECTS_NEW;

SQL> COMMIT;

重建索引

SQL> ALTER INDEX WRIADVOBJECTSIDX01REBUILD;SQL>ALTERINDEXWRI_ADV_OBJECTS_IDX_01 REBUILD;

SQL> ALTER INDEX WRIA​DVO​BJECTSI​DX0​1REBUILD;SQL>ALTERINDEXWRI_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