Restore old statistics

When you collect new statistics, oracle stores the old statistics in the data dictionary. If the new statistics lead to sub optimal plans then you can restore the old statistics.

To view the history of statistics gathering

SQL> Select * From DBA_OPTSTAT_OPERATIONS;
SQL> Select * From DBA_TAB_STATS_HISTORY;

The old statistics are purged automatically after the retention period (default 31 days). Hence you can restore the statistics to anytime in last 31 days.

To check the current retention period

SQL> Select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

To change the retention period

SQL> Exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45)

To manually purge the old statistics

SQL> Exec dbms_stats.purge_stats(Timestamp);

To Restore the statistics

SQL> Exec dbms_stats.restore_database_stats(Sysdate -1);
SQL> Exec dbms_stats.restore_dictionary_stats(Sysdate -1);
SQL> Exec dbms_stats.restore_system_stats(‘Sysdate -1’);
SQL> Exec dbms_stats.restore_schema_stats(‘SCOTT’,’Sysdate -1’);
SQL> Exec dbms_stats.restore_table_stats(‘SCOTT’,’EMP’’Sysdate -1’);

Thanks