Beginning with Oracle10G, when statistics are gathered for a table, the old statistics are retained so should there be any problem with performance of queries dependent on those statistics, the old ones can be restored.
How long does Oracle retain the statistics for ?
The default period for which statistics are retained is 31 days but this can be altered with:-
execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (xx)
- where xx is the number of days to retain them
NOTE:
There is an overhead of storage in the SYSAUX tablespace with statistics so care
should be taken not to cause the tablespace to fill with the statistics.
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
- will return the number of days stats are currently retained for.
select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
- will return the date statistics have been purged up to (so only dates newer
then this can possibly be restored to). Any request to restore stats from this
date or older will fail with: "ORA-20006: Unable to restore statistics ,
statistics history not available"
How
do I find the statistics history for a given table ?
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history
Having decided what date you know the statistics were good for, you can
use:-
execute DBMS_STATS.RESTORE_TABLE_STATS ('owner','table',date)
execute DBMS_STATS.RESTORE_DATABASE_STATS(date)
execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
execute DBMS_STATS.RESTORE_SCHEMA_STATS('owner',date)
execute DBMS_STATS.RESTORE_SYSTEM_STATS(date)
execute DBMS_STATS.RESTORE_DATABASE_STATS(date)
execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
execute DBMS_STATS.RESTORE_SCHEMA_STATS('owner',date)
execute DBMS_STATS.RESTORE_SYSTEM_STATS(date)
ie.
execute dbms_stats.restore_table_stats ('SCOTT','EMP','25-JUL-07 12.01.20.766591 PM +02:00');