How to Restore Schema Statistics in Oracle

It’s a Monday morning and when you arrive into work and the  customers start using your application, you realise that the CPU load on the  box is very high. What’s going on? What jobs ran at the weekend? What’s  changed? One answer: you gathered fresh statistics at the weekend and some of  the explain plans have changed.

The good news is that you can easily get the old statistics  back using the following procedure within the DBMS_STATS package:

DBMS_STATS.RESTORE_SCHEMA_STATS

When gathering new statistics for the schema, tables or  indexes within the database in Oracle 10g, the database saves the previous  version of these statistics in the dictionary in case you need to revert back  to them at some other time.

The statistics history retention value can be changed using:

DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (    retention       IN      NUMBER);

The default is 31 days.               Retention time parameter is specified in days

You can also use these settings:               0 – Old statistics are never saved. The automatic purge will  delete all statistics history               1 – Statistics history is never purged by automatic purge.               NULL – Change statistics history retention to default value

This call to the DBMS_STATS package will purge statistics  before a specified timestamp.

DBMS_STATS.PURGE_STATS(     before_timestamp       TIMESTAMP WITH TIME ZONE);

Now that you have successfully managed to restore your optimizer statistics to how they were before you, or the database, re-gathered them, perhaps you would like to know how to gather optimizer statistics properly in your Oracle database. You should also read about gathering system statistics in your database.

Like it, share it...

Category: Statistics