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();
Talip Hakan Ozturk's ORACLE BLOG 