Handling SM/OPTSTAT component growth in SYSAUX tablespace

From Oracle 10g onwards, there is serious bug ( As per oracle, a feature ), due to which the SYSAUX tablespace will grow continuously. There is only way to limit this growth and it is not clearly documented anywhere. Below are the notes I prepared in this regard.

Below information is applicable to all versions from 10.1.0.2 to 11.2.

Edit: Oracle has released patch 8553944  to fix this issue. Please check My Oracle Support for patch availability for your environment.

Whats the issue:

1.From 10gR1 onwards, Oracle will maintain the backup of the optimizer statistics automatically, even if you don’t ask it to do so.

2.This backup information is stored in SYSAUX tablespace and tables involved in this are as below:

WRI$_OPTSTAT_OPR                         TABLE
WRI$_OPTSTAT_AUX_HISTORY                 TABLE
WRI$_OPTSTAT_TAB_HISTORY                 TABLE
WRI$_OPTSTAT_IND_HISTORY                 TABLE
WRI$_OPTSTAT_HISTGRM_HISTORY             TABLE
WRI$_OPTSTAT_HISTHEAD_HISTORY            TABLE

3.Default retention of this information is obtained with below command:

select dbms_stats.get_stats_history_retention from dual;

4.Default value for this setting is 30 days and can be decreased using the below command:

exec dbms_stats.alter_stats_history_retention(10);

However,

5.Purging of this information is not scheduled using any job mechanism. Its not present in DBA_JOBS and DBA_SCHEDULER_JOBS. This is handled by MMON background process internally.

6.As per the description from ML Note: SYSAUX Grows Because Optimizer Stats History is Not Purged [ID 1055547.1], MMON will do this activity once in 24 hrs and if it cannot complete this in 5 mins, it leaves the task.

7.NO messages will be written in alert log or anywhere else to indicate that this purge did not happen. With every passing day, the probability of the failure increases and it will come to a stage, where automatic purging will never complete by MMON successfully.

8.Alternate provided by Oracle for this is to run, DBMS_STATS.PURGE_STATS program to purge this stats information. This can be done manually and is safe. No timeouts involved here. only a manual termination or abnormal failure will stop the purge.

What else am I missing here?

9.Even though we set the retention, it may happen that the due to purge failure, we will accumulate huge amount of statistics history in SYSAUX, making it to grow continuously.

10.As the purge failure is not written anywhere, we might miss the fact that there is a process which is consuming space regularly.

11.PURGE_STATS process will perform a delete from these tables. Hence the free space is not released back to tablespace resulting in free space fragmentation inside this table.

How do I know that I’ve this issue?

12.Find out your present retention value using the below statement:

select dbms_stats.get_stats_history_retention from dual;

13.Find out the oldest statistics history using below statement:

select dbms_stats.get_stats_history_availability from dual;

14.Compare the above two and you’ll know what to do?

What should I do now?

15.Identify the retention in the system at the earliest and change to a proper value. 30 days is default. However, lesser values would not do much harm.

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

17.Start scheduling the PURGE_STATS at the earliest and keep SYSAUX tablespace size in control.

18.Perform re-org of the related tables to reclaim the space.

Why should I care?

19.With every passing day, the size of the table grows bigger and bigger, finally becoming a top space consumer. Efforts to be put in for cleanup will increase exponentially . DO it to day in 1hr or do it in 5hr tomorrow.

20.PURGE_STATS procedure does a plain delete from command with a specific where clause. This leaves lot of mess as the process will complete fully or doesn’t do any thing at all. No COMMITS at regular intervals in this code.

21.For an average database of size 150G, in 6 months time , you may end up with 100M rows in this table. Clean up of this much data is always a pain.

Any tips?

22.Identify what is the oldest information you have in the stats history using the below command.

select dbms_stats.get_stats_history_availability from dual;

23.If the information is too old, start purging the information in smaller chunks. remember that if you cancel the process in between, it will do a complete rollback before coming out.

24.Use the below command to know on which dates there was huge stats generation.

select trunc(SAVTIME),count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY group by  trunc(SAVTIME) order by 1;

25.Start with oldest date and invoke below command

exec dbms_stats.purge_stats(to_date('01-JAN-2010','DD-MON-YYYY'));

NOTE: Please note that this process is resource intensive. Do not perform in business hours.

26.Once the amount of data comes down to a reasonable limit schedule it in off-peak hours, as a regular maintanance activity.

27.Keep an eye on the amount of fragmentation and consider re-org process if needed.

Edit: 1/6/2016. Please do review below MOS note if the issue is still not resolved after applying patches mentioned above.. SYSAUX Statistics History Tables are Still Growing Even After Application of Patch 14373728 (Doc ID 1528085.1)