Табличное пространство SYSAUX

Проблема наблюдается рост ТП SYSAUX (версия Oracle 12 и выше................)

Oracle Database 12.2 -- SYSAUX space usage is high, WRI$_ADV_OBJECTS is top consumer

Problem

SYSAUX tablespace usage is high 

Top occupants are:

col occupant_name for a15
select  occupant_name, space_usage_kbytes
from v$sysaux_occupants
order by space_usage_kbytes desc;
 
OCCUPANT_NAME   SPACE_USAGE_KBYTES
--------------- ------------------
SM/ADVISOR                19599360
SM/OPTSTAT                  780544
AUDSYS                      553280

Top segments:

col segment_name for a30
select segment_name, sum(bytes) bytes
from dba_Segments
where tablespace_name='SYSAUX'
group by segment_name
order by bytes desc
fetch first 5 rows only;
 
SEGMENT_NAME                          BYTES
------------------------------ ------------
WRI$_ADV_OBJECTS                10692329472
WRI$_ADV_OBJECTS_IDX_01          5522849792
WRI$_ADV_OBJECTS_PK              3841982464
AUD$UNIFIED                       562429952
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST    285212672

Oracle Database 12.2 introduced the Optimizer Statistics Advisor, which is aimed to analyze and advise on the known issues related to gathering statistics

The Optimizer Statistics Advisor is run automatically as a task named AUTO_STATS_ADVISOR_TASK during maintenance windows as part of automatic statistics gathering client 

col task_name for a30
select task_name, count(*)
from dba_advisor_objects
group by task_name
order by 2 desc
fetch first 5 rows only;

TASK_NAME                        COUNT(*)
------------------------------ ----------
AUTO_STATS_ADVISOR_TASK          92223191
SYS_AUTO_SPCADV506002204052023         20
SYS_AUTO_SPCADV107002202052023         15
SYS_AUTO_SPCADV806002208052023         14
SYS_AUTO_SPCADV306002227042023         14

Due to large number of runs, considerable amount of space used in SYSAUX and will keep growing

Solution

How to Purge WRI$_ADV_OBJECTS the Recommend Way

In versions below 19.1, the parameter which controls automatic purging of old records of the Optimizer Statistics Advisor is set to UNLIMITED

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

The parameter is modifiable beginning with versions:

• Patch 30138470 12.2.0.1.191015 (Oct 2019) Database Release Update (DB RU)
• Patch 28822489 18.5.0.0.190115 (Jan 2019)
• 19.1.0

With the above mentioned versions available, the default value for the EXECUTION_DAYS_TO_EXPIRE parameter is set to 30 days, and old records will be automatically purged.

Also, auto purging can be forced with:

SQL> exec prvt_advisor.delete_expired_tasks;

Note. In a PDB the auto purging is not working, and the above mentioned command is required to be run manually regularly

The parameter can be adjusted also:

begin
    DBMS_ADVISOR.SET_TASK_PARAMETER(
        task_name=> 'AUTO_STATS_ADVISOR_TASK',
        parameter=> 'EXECUTION_DAYS_TO_EXPIRE',
        value => 10);

    prvt_advisor.delete_expired_tasks;
end;
/

In case of very large segments, huge amount of UNDO will be required, in this case some scripted iterative approach will be required to purge the data incrementally by days

After the purging, re-org the segments to release space:

SQL> alter table WRI$_ADV_OBJECTS move;
SQL> alter index WRI$_ADV_OBJECTS_PK rebuild;
SQL> alter index WRI$_ADV_OBJECTS_IDX_01 rebuild;
SQL> alter index WRI$_ADV_OBJECTS_IDX_02 rebuild;

How to Purge WRI$_ADV_OBJECTS the Manual Way

In case the recommended way described above is not possible, purge the old records manually to release the space

1) Check the number of rows in WRI$_ADV_OBJECTS not pertaining to AUTO_STATS_ADVISOR_TASK:

SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS
     WHERE TASK_ID != (
        SELECT ID FROM WRI$_ADV_TASKS
        WHERE NAME='AUTO_STATS_ADVISOR_TASK');

2) Create a backup table to temporary keep the records NOT pertaining to the AUTO_STATS_ADVISOR_TASK:

SQL> CREATE TABLE WRI$_ADV_OBJECTS_NEW
     AS SELECT * FROM WRI$_ADV_OBJECTS
     WHERE TASK_ID != (
        SELECT ID FROM WRI$_ADV_TASKS
        WHERE NAME='AUTO_STATS_ADVISOR_TASK');

SQL> SELECT COUNT(*) FROM WRI$_ADV_OBJECTS_NEW;

3) Truncate the table:

SQL> TRUNCATE TABLE WRI$_ADV_OBJECTS;

4) Restore the records of the table:

SQL> INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS
     SELECT * FROM WRI$_ADV_OBJECTS_NEW;

SQL> COMMIT;

Note. 19c version introduced the virtual column SQL_ID_VC, so the star (*) will not work, and will require to list all the columns in the statement excluding the SQL_ID_VC   column, otherwise ORA-54013 will be raised

How to disable the Optimizer Statistics Advisor

1) Possibility to control the autotask of the Optimizer Statistics Advisor was introduced only in 21c release. For versions below 21c release, a backport patch can be requested through Oracle Support

With this fix the autotask can be checked with:

SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;

DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
-----------------------------------------------
TRUE

To disable/enable:

SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');

SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;

DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
-----------------------------------------------
FALSE

2) Without the fix on the versions below 21c, the autotask can be dropped:

SQ> EXEC DBMS_STATS.DROP_ADVISOR_TASK('AUTO_STATS_ADVISOR_TASK');

But in this case the ORA-20001: Statistics Advisor: Invalid Task Name For the current user error can be observed later when task is referenced 

To recreate/enable the autotask:

SQL> EXEC DBMS_STATS.INIT_PACKAGE;

3) The safest way is to filter out all the rules for the autotask, it will keep the task enabled, but will restrain space usage, since all the rules for all operations will be disabled: 

DECLARE
    filter1 CLOB;
BEGIN
    filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
        'AUTO_STATS_ADVISOR_TASK', NULL, NULL,'DISABLE');
END;
/

Rules can be viewed with V$STATS_ADVISOR_RULES

Reference

• Optimizer Statistics Advisor In 12.2 (Quick Overview) (Doc ID 2259398.1)
• How To Disable Optimizer Statistics Advisor From 12.2 Onwards (Doc ID 2686022.1)
• How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)
• SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)