Make sure you backup old version of statistics before you start collecting new statistics.

I believe very few DBAs backup old table/schema/database statistics before or while they collecting new statistics. I guess that majority of the DBAs won't backup existing/available statistics before or while collecting new ones.

If you don't backup old version of statistics, and optimizer behave differently because of new statistics, there you are in trouble. If you have backup of old version of statistics, you can restore them to have optimizer behaviour back in time.

Many Oracle experts and Oracle itself now recommends backup the old statistics before collecting new one. So that you will have an option to restore the previous version of statistics.

There are couple of workarounds to backup old version of statistics in earlier version of Oracle.

In Oracle 10g.

From Oracle10g onwards, whenever statistics are modified, old version of statistics are saved automatically. (If you use analyze in 10g to collect statistics, automatic saving of old version statistics won't take place).

DBA_OPTSTAT_OPERATIONS, dictionary view contains history of statistics operations performed at various level, table/schema/database.

OPERATION VARCHAR2(64)
TARGET VARCHAR2(64)
START_TIME TIMESTAMP(6) WITH TIME ZONE
END_TIME TIMESTAMP(6) WITH TIME ZONE

Using start_time and end_time criteria from the above dictionary view, you can always restore old version of statistics.
Use DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY in order to get the oldest time stamp where statistics history is available, older than given timestamp from the above procedure is not possible.

PURGE_STATS procedure can be used to purge the old statistics manually.

The old statistics are purged automatically at regular intervals based on statistics history retention setting. The default retention value is set for 31 days. If you would like to modify the default for the history retention settings, use DBMS_STATS.ALTER_STATS_HISTORY_RETENTION.

Note : Automatic purging has relation with STATISTICS_LEVEL parameter setting. It must be either ALL/TYPICAL to avail this feature in 10g.

In previous versions of Oracle, using dbms_stats.create_stat_table you can create a table to store old version of statistics.

Jonathan Lewis has discussed with good examples on how to create the table to store old version of statistics on his blog, 'Saving Statistics'.
http://jonathanlewis.wordpress.com/2006/12/03/saving-statistics/


The other way of backup and restore of old statsitics - Exporting and Importing Statistics.

Statistics can be exported and imported. This enables to copy statistics from production test/development database.

You need a table to hold statistics before you start exporting statistics. Using dbms_stats.create_stat_table procedure to create the table. DBMS_STATS.EXPORT_*_STATS and DBMS_STATS.IMPORT_*_STATS procedures are used to export/import statistics.

I hope that I do follow this procedure with my databases, which are not on 10g.

Happy reading,


Jaffar

After reading Jonathan's blog on SAVING STATSITICS, I thought of discussing little bit of this at my blog.