SYSAUX tablespace growing rapidly

Saturday, December 3, 2011

I have an Oracle 11g R2 ( database where I noticed the SYSAUX tablespace was growing larger every day.  Searched Oracle My Support and found Doc ID 1292724.1 and Doc ID 552880.1 which were helpful.

After running awrinfo.sql, I found the largest consumer to be SM/OPTSTAT at 2.8 GB which is larger and not typical when compared to my other Oracle 10g R2 and Oracle 11g R1 databases.

(1b) SYSAUX occupants space usage (v$sysaux_occupants)  
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| SM/OPTSTAT           SYS                        2,726.8 MB
| SM/AWR               SYS                           76.6 MB
| SM/ADVISOR           SYS                            8.2 MB
| LOGMNR               SYSTEM                         7.9 MB
| SM/OTHER             SYS                            6.8 MB
| JOB_SCHEDULER        SYS                            3.6 MB

(4) Space usage by non-AWR components (> 500K)

COMPONENT        MB SEGMENT_NAME                            SEGMENT_TYPE  
--------- --------- --------------------------------------- --------------
NON_AWR       800.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST      INDEX         
NON_AWR       424.0 SYS.I_WRI$_OPTSTAT_H_ST                 INDEX         
NON_AWR       192.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST       INDEX         
NON_AWR       104.0 SYS.I_WRI$_OPTSTAT_HH_ST                INDEX

Searching Google, I came upon John Hallas’ “Purging statistics from the SYSAUX tablespace” blog entry quite informative and detailed on managing occupants in the SYSAUX tablespace.

As John mentions:

“Whenever statistics in the dictionary are modified, old versions of statistics are saved automatically for future restoring. The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of recent statistics gathering performed in the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure. The default value is 31 days.”

To help reduce the amount of space caused by historical data, I set the retention of old statistics to 14 days.

exec dbms_stats.alter_stats_history_retention(14);

To verify the new setting, I executed the statement below.

select dbms_stats.get_stats_history_retention from dual;

I proceeded to incrementally purge statistics from 31 days to 14 days.  Started with 28 days, then moved to 21 days.

exec dbms_stats.purge_stats(sysdate-28);

Then I tried 14 days, however I encountered an UNDO space error requiring me to change my increments using 18, 16, and finally 14 to complete the purge task.

Now to reclaim the space used by the objects shown in section 4 of the AWRINFO report, I will eventually follow John Hallas’ steps using the ALTER TABLE MOVE command.