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.
This post outlines the facility by which statistics may be restored following their removal. This facility is available from Orale 10g onwards.
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.
How do I know how many days the statistics are available for?
Below query will return the number of days stats are currently retained for.
sql> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
Below query 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”
sql> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
How do I find the statistics history for a given table?
Below query will show the times statistics were regathered for a given table:
sql> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history