升级DB到12.2.0.1版本之后，由于统计信息顾问导致SYSAUX 过快增长

郑全

升级DB到12.2.0.1版本之后，由于统计信息顾问导致SYSAUX 过快增长 (Doc ID 2440139.1)

文档内容

症状

更改

原因

解决方案

参考

适用于:

Oracle Database Exadata Express Cloud Service - 版本 N/A 和更高版本

Oracle Database Cloud Schema Service - 版本 N/A 和更高版本

Oracle Database Cloud Service - 版本 N/A 和更高版本

Oracle Database Backup Service - 版本 N/A 和更高版本

Oracle Database - Enterprise Edition - 版本 12.2.0.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

...

更改

升级数据库到12.2.0.1 或更高 (如18c/19c 等).

原因

这个是由于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

...

可以看到统计信息顾问任务运行了27082431次，所以消耗了SYSAUX大量的空间。

若是想了解优化器统计信息顾问相关内容，请参考如下文档：

Document 2259398.1 Optimizer Statistics Advisor In 12.2

解决方案

1. 请参考如下文档进行旧数据的清理：

Document 2660128.1 How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards

2.这个自动运行的任务(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 MOVE;

SQL> ALTER INDEX 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_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_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;

/

DBA可以在任何时间重建统计信息顾问任务 (AUTO_STATS_ADVISOR_TASK)，具体参考如下办法：

SQL> EXEC DBMS_STATS.INIT_PACKAGE();

The Optimizer Statistics Advisor Task (AUTO_STATS_ADVISOR_TASK) will NOT be disabled with the below command. This does not stop the execution of AUTO_STATS_ADVISOR_TASK itself. This is only effective in the point of view of reducing the increase of SYSAUX space segments as rule filters are disabled for all operations of Statistics Advisor.

运行如下命令，统计信息顾问任务 (AUTO_STATS_ADVISOR_TASK) 不会被DISABLE. 这不会停止任务AUTO_STATS_ADVISOR_TASK自身，只是把它作为控制SYSAUX 空间增长的过滤规则停掉。

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.