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