Sysaux Tablespace Growing Rapidly in 12cR2

Solution:  Purge Optimizer Statistics Advisor's Old Records

Check the Top space occupied objects 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<=10;

SEGMENT_NAME                   OWNER      TABLESPACE   SIZE(MB) SEGMENT_TYPE
------------------------------ ---------- ---------- ---------- ---------------
WRI$_ADV_OBJECTS               SYS        SYSAUX           2176 TABLE
WRI$_ADV_OBJECTS_IDX_01        SYS        SYSAUX           1094 INDEX
WRI$_ADV_OBJECTS_PK            SYS        SYSAUX            709 INDEX
SYS_LOB0000007350C00005$$      SYS        SYSAUX        88.1875 LOBSEGMENT
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST SYS        SYSAUX             88 INDEX
SYS_LOB0000067413C00006$$      MDSYS      SYSAUX         50.125 LOBSEGMENT
I_WRI$_OPTSTAT_H_ST            SYS        SYSAUX             45 INDEX
WRH$_SYSMETRIC_HISTORY_INDEX   SYS        SYSAUX             27 INDEX
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST  SYS        SYSAUX             23 INDEX
WRH$_SYSMETRIC_HISTORY         SYS        SYSAUX             21 TABLE PARTITION

WRI$_ADV_OBJECTS - This is the table occupies more space in the tablespace,due to optimizer statistics advisor run the task AUTO_STATS_ADVISOR_TASK regularly.             

Check number of records of the task.

SQL> col TASK_NAME format a25

SQL> select TASK_NAME,, COUNT(*) from dba_advisor_objects group by TASK_NAME order by 2 desc;

TASK_NAME                             COUNT(*)
----------------------------------- ----------
AUTO_STATS_ADVISOR_TASK               19212267
...
...
...

65 rows selected.

Check the retention number of days the records to keep in.

SQL> col TASK_NAME format a25
SQL> col parameter_name format a35
SQL> col parameter_value format a20
SQL> set lines 120
SQL> select TASK_NAME,parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE';

 

TASK_NAME                 PARAMETER_NAME                      PARAMETER_VALUE
------------------------- ----------------------------------- --------------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE            UNLIMITED

Also, you may check the entries

SQL> col task_name format a25

SQL> col EXECUTION_NAME format a15

SQL> select TASK_ID,TASK_NAME,EXECUTION_NAME ,execution_start from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK';

   TASK_ID TASK_NAME                 EXECUTION_NAME  EXECUTION

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

        23 AUTO_STATS_ADVISOR_TASK   EXEC_95         04-NOV-19

        23 AUTO_STATS_ADVISOR_TASK   EXEC_97         04-NOV-19

        23 AUTO_STATS_ADVISOR_TASK   EXEC_99         05-NOV-19

        23 AUTO_STATS_ADVISOR_TASK   EXEC_109        06-NOV-19

.....

.....

        23 AUTO_STATS_ADVISOR_TASK   EXEC_3140       01-NOV-20
        23 AUTO_STATS_ADVISOR_TASK   EXEC_3147       02-NOV-20
        23 AUTO_STATS_ADVISOR_TASK   EXEC_3157       03-NOV-20

366 rows selected.

As the value is unlimited , there is no expiry has been set to.  Hence,  we will set as per our requirement. Here, we set 90 days of records to keep.

SQL> EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 90);

PL/SQL procedure successfully completed.

We can delete manually or we could leave it to auto purge.

Here, we test the manual purge.

SQL> exec prvt_advisor.delete_expired_tasks; 

 

PL/SQL procedure successfully completed.

Please note the difference after purge the records.

SQL> col TASK_NAME format a25

SQL> select TASK_NAME,, COUNT(*) from dba_advisor_objects group by TASK_NAME order by 2 desc;

 

TASK_NAME                             COUNT(*)

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

AUTO_STATS_ADVISOR_TASK               3028810

Note: The manual purge will generate more undo if there are huge number of records.