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 deta
No comments:
Post a Comment