Oracle Worklog

Just another WordPress.com weblog

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

July 24, 2010 - Posted by | Database Monitoring | , ,

10 Comments »

  1. Very useful Ravi.

    Thank you.

    Comment by Bharath | July 30, 2010 | Reply

  2. [...] The busiest day of the year was November 16th with 26 views. The most popular post that day was Handling SM/OPTSTAT component growth in SYSAUX tablespace. [...]

    Pingback by 2010 in review « Oracle Worklog | January 2, 2011 | Reply

  3. Excellent tip. Very helpful. Thanks Ravi

    Comment by Jay | March 8, 2011 | Reply

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

    Comment by Mani Ganapathy | August 23, 2011 | Reply

  5. Great little article. Saved me some time. Thanks a ton.

    Comment by Riley | September 28, 2011 | Reply

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

    Comment by Thorsten | October 24, 2011 | Reply

    • Thanks for stopping by. You may also find the other post on SM/OPTSTAT in this blog useful, if you want to clean up the space gracefully. If you would like to purge it all, there is another post by another dba at http://jhdba.wordpress.com/2009/05/19/purging-statistics-from-the-sysaux-tablespace/ , which you might find useful.

      Comment by Ravi Madabhushanam | October 25, 2011 | Reply

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

        Comment by Thorsten | November 17, 2011

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

        Comment by Ravi Madabhushanam | November 19, 2011

  7. [...] Handling SM/OPTSTAT component growth in SYSAUX tablespace gives an excellent overview on the problem with the historical CBO statistics. [...]

    Pingback by Oracle database 11gR2 upgrades: advanced tips and best practices « Julian Dontcheff's Database Blog | February 23, 2012 | Reply


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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: