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

Kinein SYSDBA

Tuesday, November 3, 2020