How To Clean SYSAUX Tablespace? Purging data from WRI$_ADV_OBJECTS

Hello,

SYSAUX Tablespace grows rapidly and consumes more space. Querying V$SYSAUX_OCCUPANTS shows SM/ADVISOR on top.

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;

And further querying DBA_SEGMENTS shows WRI$_ADV_OBJECTS consume more space in SYSAUX.

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 COL TASK_NAME FORMAT A35
SQL> SELECT TASK_NAME, COUNT(*) CNT FROM DBA_ADVISOR_OBJECTS GROUP BY TASK_NAME ORDER BY CNT DESC;

The Statistics Advisor Task(AUTO_STATS_ADVISOR_TASK) can be dropped to release the space occupied by that advisor output data.

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

Once the task is dropped then all of its dependent task output data would be deleted from the WRI$_ADV_OBJECTS table. Now, let me reorganize the table WRI$_ADV_OBJECTS and its indexes after dropping the 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;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;

You must ensure all indexes are valid.

SQL> column index_name format a30
SQL> select index_name,status from dba_indexes where table_name='WRI$_ADV_OBJECTS' ;

For Multitenant/PDB environment, reorganize the table and its indexes as follows:

SQL> alter session set container=;
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');

Dropping the task AUTO_STATS_ADVISOR_TASK would cause errors as follows when the task name is referenced.

ORA-20001: Statistics Advisor: Invalid Task Name For the current user
If the above error happens, then recreate the AUTO_STATS_ADVISOR_TASK as follows to resolve the errors:

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

Recreating the task would again cause SYSAUX space to grow. Check whether the Auto Statistics Advisor task is created

SQL> select name, ctime, how_created, OWNER_NAME from sys.wri$_adv_tasks where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

If there are too many records in the table WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK, then huge UNDO would be required. In such cases, following method can be implemented to purge the data by avoiding excessive redo/undo generation.
Check the number 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');

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;

Now, 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;

Note that, 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;

Drop the statistics advisor task from dictionary to refrain from executing.

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

The Optimizer Statistics Advisor Task (AUTO_STATS_ADVISOR_TASK) can be recreated any time by running the following if DBA(s) wants to use the Statistics Advisor.

SQL> EXEC DBMS_STATS.INIT_PACKAGE();

ORA-20001: Statistics Advisor: Invalid task name for the current user

Hello,

Sometimes, in alert log we are getting below error:

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_43482"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197

Issue occurred while creating database with DBCA, the advisory package is not created properly. This issue is happening because of the unavailability of the Stats Advisor Tasks from the created database. It is Oracle known Bug and can appear when database is created with dbca in oracle 12.2

To solve this issue;

Connect with sysdba privilege user and run following query:

col name for a30
select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'
and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

no rows selected.

To solve this issue;

EXEC dbms_stats.init_package();

And let me verify the package create. It will fixed the issue

col name for a30
select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'
and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
NAME                          |CTIME    |HOW_CREATED
------------------------------|---------|------------------------------
AUTO_STATS_ADVISOR_TASK       |17-APR-19|CMD
INDIVIDUAL_STATS_ADVISOR_TASK |17-APR-19|CMD