标题: SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2 [打印本页] 作者: 郑全 时间: 2018-3-23 15:57 标题: 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;
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;
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;
/