WRI$_ADV_OBJECTS表过大,导致PDB的SYSAUX表空间不足

现象
监控发现sysaux表空间使用不断增加，导致表空间不足

 

查看过程

查看版本：

?

1 2 3 4 5 6 7 8 9 10 11

SQL> select * from v$version;   BANNER                                                                               CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 PL/SQL Release 12.2.0.1.0 - Production                                                    0 CORE    12.2.0.1.0      Production                                                        0 TNS for Linux: Version 12.2.0.1.0 - Production                                            0 NLSRTL Version 12.2.0.1.0 - Production                                                    0   SQL>

查看v$sysaux_occupants，发现SM/ADVISOR排在第一

?

1 2 3

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;

查看dba_segments，发现WRI$_ADV_OBJECTS占用最大

?

1 2 3 4 5

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 segment_name,owner,tablespace_name,bytes/1024/1024 "SIZE(MB)",segment_type from dba_segments where tablespace_name='SYSAUX' order by bytes desc;

也可以通过awrinfo查看。

 

原因

因为在12.2中，引入了新的特性：optimizer statistics advisor。优化器统计信息顾问每天都会在维护窗口运行，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 ----------------------------------- ---------- SYS_AUTO_SQL_TUNING_TASK                 20703 AUTO_STATS_ADVISOR_TASK                   9881

　　

解决方案

方案1.删除statistics advisor task(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; /

一旦任务被删除，与任务相关的结果数据都会从表WRI$_ADV_OBJECTS删除。
在删除任务的过程中，可能会遇到下面的错误：

?

1	ORA-20001: Statistics Advisor: Invalid Task Name For the current user

如果遇到上面的错误，可以先重建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;

 

方案2.如果表WRI$_ADV_OBJECTS比较大，删除任务AUTO_STATS_ADVISOR_TASK会需要很多的undo表空间
可以通过以下方式purge数据，不会过度的产生redo/undo数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

### Check the no.of rows in WRI$_ADV_OBJECTS for 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');     COUNT(*) ----------   46324479   ### Do CTAS from WRI$_ADV_OBJECTS to keep the rows apart from 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;     COUNT(*) ----------        359   ### Truncate the table ### SQL> truncate table wri$_adv_objects;   ### Insert the rows from backed up table WRI$_ADV_OBJECTS_NEW to restore the records of ther advisor objects ### SQL> insert /*+ APPEND */ into wri$_adv_objects select * from wri$_adv_objects_new; SQL> commit; SQL> drop table wri$_adv_objects_new;   ### Reorganize the indexes ### SQL> alter index wri$_adv_objects_idx_01 rebuild; SQL> alter index wri$_adv_objects_pk rebuild;

　　

其它

重建AUTO_STATS_ADVISOR_TASK

Optimizer Statistics Advisor Task (AUTO_STATS_ADVISOR_TASK)可以在任何时刻进行重建 

?

1	SQL> EXEC DBMS_STATS.INIT_PACKAGE();

　

也可以禁用该任务，而不是删除

?

1 2 3 4 5 6 7 8 9

declare   filter1 clob; begin   filter1 := dbms_stats.configure_advisor_rule_filter('AUTO_STATS_ADVISOR_TASK',                                                       'EXECUTE',                                                       NULL,                                                       'DISABLE'); END; /

　　

参考文档：SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor (Doc ID 2305512.1)