Rapid Growth of SYSAUX Tablespace

Oracle 10g onwards, The SYSAUX an auxiliary tablespace to the SYSTEM tablespace created in your database.
Some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace.
Below table shows the components that uses the SYSAUX tablespace as their default tablespace and the tablespace in which they were stored in earlier releases.

Component Using SYSAUX	Tablespace in Earlier Releases
Analytical Workspace Object Table	SYSTEM
Enterprise Manager Repository	OEM_REPOSITORY
LogMiner	SYSTEM
Logical Standby	SYSTEM
OLAP API History Tables	CWMLITE
Oracle Data Mining	ODM
Oracle Spatial	SYSTEM
Oracle Streams	SYSTEM
Oracle Text	DRSYS
Oracle Ultra Search	DRSYS
Oracle interMedia ORDPLUGINS Components	SYSTEM
Oracle interMedia ORDSYS Components	SYSTEM
Oracle interMedia SI_INFORMTN_SCHEMA Components	SYSTEM
Server Manageability Components
Statspack Repository	User-defined
Oracle Scheduler
Workspace Manager	SYSTEM

The statistics are stored in the data dictionary. Now whenever statistics in the dictionary are modified, old versions of statistics are saved automatically for future restoring. This history information is stored in SYSAUX tablespace and tables involved in this are as below:
WRI$_OPTSTAT_OPR                    
WRI$_OPTSTAT_AUX_HISTORY                
WRI$_OPTSTAT_TAB_HISTORY                
WRI$_OPTSTAT_IND_HISTORY                
WRI$_OPTSTAT_HISTGRM_HISTORY            
WRI$_OPTSTAT_HISTHEAD_HISTORY         
 

By default, the MMON performs the automatic purge that removes all stats history older than the following:

* current time - statistics history retention (by default 31 days)
* time of recent analyze in the system - 1

MMON performs the purge of the optimizer stats history automatically, but it has an internal limit of 5 minutes to perform this job.MMON will do this activity once in 24 hrs. If the operation takes more than 5 minutes, then it is aborted and stats not purged. No trace or alert message is reported. Because of this, as time elapse more data will be accommodated in above tables.
Statistics history retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure.

I have an Oracle 11g R2 database where I noticed the SYSAUX tablespace was growing larger every day.   After running $ORACLE_HOME/rdbms/admin/awrinfo.sql (Doc ID 1292724.1),
I found the largest consumer to be SM/OPTSTAT(9.5GB) and SM/AWR(1GB) as shown below.

  (1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name    Schema Name     Space Usage
| -------------------- -------------------- ----------------
| SM/OPTSTAT           SYS                        9,415.1 MB
| SM/AWR                    SYS                        1,005.3 MB
| SM/ADVISOR           SYS                        188.5 MB
| XDB                             XDB                       125.8 MB
| EM                              SYSMAN                 82.3 MB


(3b) Space usage within AWR Components (> 500K)
**********************************
COMP        MB     SEGMENT_NAME - % SPACE_USED                                                                                            SEGMENT_TYPE
--------- --------- ---------------------------------------------------------------------------------------------------  ---------------
ASH           382.0  WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_3563904870_4350 -  97%  TABLE PARTITION
ASH            38.0  WRH$_ACTIVE_SESSION_HISTORY_PK.WRH$_ACTIVE_3563904870_4350 - 98%  INDEX PARTITION

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

COMPONENT        MB SEGMENT_NAME                                                          SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR     3,220.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST                    INDEX
NON_AWR     2,905.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY                TABLE
NON_AWR     1,930.0 SYS.I_WRI$_OPTSTAT_HH_ST                                          INDEX
NON_AWR       448.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                   INDEX
NON_AWR       296.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY                    TABLE
NON_AWR       232.0 SYS.I_WRI$_OPTSTAT_H_ST                                               INDEX
NON_AWR       168.0 SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST                             INDEX
NON_AWR       141.0 SYS.SYS_LOB0000006306C00038$$                               LOBSEGMENT
NON_AWR       104.0 SYS.I_WRI$_OPTSTAT_TAB_ST                                          INDEX


To resolve this:

1. Turn off the Autoextend on the SYSAUX at the earliest to ensure that the tablespace doesn’t grow out of bounds and finally become complete unmanageable.

2. Manually purge old statistics using DBMS_STATS.purge_stats as below

Find out your present retention value using the below statement
SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

Find out the oldest statistics history using below statement(Shows available stats that have not been purged):
 
SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
05-MAR-14 05.31.04.053232000 AM +05:30

Set retention of old stats to less number of days. I set here it to 10 days as below.

SQL> exec dbms_stats.alter_stats_history_retention(10);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         10
Purge stats older than 10 days. Best to do this in stages if there is a lot of data (sysdate-10) since it consumes more resources. Do this during less activity on the database. This purge will delete data from WRI$ tables.

Below command will purge stats which are older than 10 days.

SQL> exec dbms_stats.purge_stats(sysdate-10);

PL/SQL procedure successfully completed.

Below command shows available stats that have not been purged

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
30-MAR-14 09.46.46.000000000 AM +05:30

Once purge is done, reorg these tables to release space to the database. Refer ID 1271178.1 for more details.