Wri$_adv_objects table too large, resulting in insufficient sysaux table space for the PDB

wri$_adv_objects table is too large, which leads to insufficient sysaux table space in PDB. How to solve this problem?

Phenomenon
Monitoring finds Sysaux table space usage increasing, resulting in insufficient table space

Viewing procedures

View version:

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> 

Check out the v$sysaux_occupants and find Sm/advisor ranked first

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;

View dba_segments and discover Wri$_adv_objects occupies the largest

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;

can also be viewed through awrinfo.

Reason

Because in 12.2, a new feature was introduced: Optimizer statistics advisor. The optimizer statistics advisor runs daily in the Maintenance window, auto_stats_advisor_task multiple times, and consumes a large amount of sysaux table space.

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

Solution Solutions





Scenario 1. 

Delete Statistics Advisor Task (auto_stats_advisor_task), delete the task and then release the data from the Statistics advisor

Delete the task directly:

declare
  v_tname varchar2(32767);
begin
  v_tname := ‘AUTO_STATS_ADVISOR_TASK‘;
  dbms_stats.drop_advisor_task(v_tname);
end;
/

Once the task is deleted, the result data related to the task is deleted from the table wri$_adv_objects.
During the removal of a task, you may encounter the following error:

Ora-20001:statistics advisor:invalid Task Name for the current user

If you encounter the above error, you can first rebuild the Auto_stats_advisor_task to resolve the problem:

SQL> connect / as sysdba
SQL> EXEC DBMS_STATS.INIT_PACKAGE();

Reorganize tables and indexes after you delete a task

SQL> alter table wri$_adv_objects move;
SQL> alter index wri$_adv_objects_idx_01 rebuild;
SQL> alter index wri$_adv_objects_pk rebuild;

Scenario 2. If the table wri$_adv_objects is larger, deleting the task Auto_stats_advisor_task will require a lot of undo table space
Data can be purge in the following ways, without excessive redo/undo data generation

### 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;

Other

Rebuilding Auto_stats_advisor_task

Optimizer Statistics Advisor Task (auto_stats_advisor_task) can be rebuilt at any time

Sql> EXEC dbms_stats. Init_package ();

You can also disable the task instead of deleting

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

Reference Document: Sysaux tablespace grows rapidly after upgrading Database to 12.2.0.1 Due to Statistics Advisor (Doc ID 2305512.1)

Wri$_adv_objects table too large, resulting in insufficient sysaux table space for the PDB

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch