SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor

SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor

 

Problem Statement :

-----------------------------

 

SYSAUX Tablespace grows rapidly and consumes more space after upgrading the database to 12.2.0.1.

 

 

Querying V$SYSAUX_OCCUPANTS shows SM/ADVISOR on top.

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

 

Example:

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

...

 

Root Cause:

----------------

 Optimizer Statistics Advisor, a new feature in 12.2 that runs everyday during the maintenance window causing the problem.

AUTO_STATS_ADVISOR_TASK which runs huge no.of times causing the SYSAUX space to go up.

 

Example:

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

...

There are lot of executions for the Statistics Advisor task in 12.2 and consumes more space in SYSAUX tablespace.

 

Solution :

--------------

 

ð 1

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.

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> connect / as sysdba

SQL> EXEC DBMS_STATS.INIT_PACKAGE();

 

Reorganize the table 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;

 

ð 2

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 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');

 

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

 

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

### Reorganize the indexes ###

SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;

SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

 

P.S: Table WRI$_ADV_OBJECTS_NEW can be dropped after all the above commands executed successfully.

 

 

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();

The Optimizer Statistics Advisor Task (AUTO_STATS_ADVISOR_TASK) can also be disabled rather than dropping as follows to avoid executing and providing recommendations.

DECLARE

filter1 CLOB;

BEGIN

filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER('AUTO_STATS_ADVISOR_TASK','EXECUTE',NULL,'DISABLE');

END;

/