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