SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2

郑全

[size=130%]SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor (文档 ID 2305512.1)
[size=130%]


[size=130%]


Applies to:   Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Oracle Database - Standard Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

SymptomsSYSAUX 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
...



Changes Upgrading the database to 12.2.0.1.
CauseOptimizer 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.

Refer the following document for Optimizer Statistics Advisor Framework:-
Document 2259398.1 Optimizer Statistics Advisor In 12.2


Solution1. 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;
/