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.

About these ads

10 thoughts on “Handling SM/OPTSTAT component growth in SYSAUX tablespace

  1. Clearly illustrated. Saved a lot of time compiling multiple ML doc ids and google posts. Thanks indeed for your efforts.

  2. Great Article, now i know why some databases have humungous SYSAUX-Tsps while others are satisfied with small 4GB ones :). Thanks a lot.

      • We researched the issue a bit further and found OPatch/Bug 8553944 which solves the issue, it was able to clean up a gigantic SYSAUX with data back to 2009 over just a weekend, which shows that given enough time, sometimes Oracle gets something right.

      • Thanks lot for referring the bug details here Thorsten.

        Once the problem has been solved manually, we did not check again on the interim fix. As have pointed it out patch 8553944 will certainly fix the problem. This has been originally released on Jul 14, 2010 for 11.1.0.7 and since then it has been released for many other versions as well.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s