Pages

Thursday 6 December 2012

SYSAUX tablespace growing rapidly



From Oracle 10gR1 onwards, there is serious bug(14373728 and 8553944), due to which the SYSAUX tablespace will grow continuously. This issue is fixed in 12.1 release

Why It grows?

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.


How to Identify

I have an Oracle 11g R2 (11.2.0.3) 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.

Note: When collected data for AWR and similar OP_STAT tables reaches an internally defined threshold volume of data Oracle will automatically create partitions . This AWR, SQLSETs and similar data is then stored in partitions including WRH/WRI based objects.


(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)
**********************************


COMPONENT        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


How to resolve

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

select dbms_stats.get_stats_history_retention from dual;

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):

select dbms_stats.get_stats_history_availability from dual;

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
05-NOV-12 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.

exec dbms_stats.alter_stats_history_retention(&days);

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-30,sydate-28 etc)since it consumes more resources. Do this activity during less activities on the database. This purge will delete data from WRI$ tables.

Below command will purge stats which is older than 28 days.

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


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
---------------------------------------------------------------------------
08-NOV-12 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.










3 comments:

  1. Thank you so much. This is a great blog and the steps worked very well. Saved me some space in my databases.

    ReplyDelete
  2. Awesome explanation.

    ReplyDelete