Restoring previous version of stats

Restoring previous version of stats

Scenario : Performance of certain queries has deteriorated suddenly after collecting fresh statistics . You want to revert the stats to previous values .

Solution : Use the dbms_stats.restore_stats procedure to revert to an older set of optimizer statistics .

1. Check the retention of stats for your database

SQL > select dbms_stats.get_Stats_history_retention from dual

GET_STATS_HISTORY_RETENTION
--------------------------------------------------
                              31

2. Check how far you can go to restore the stats :

SQL> select dbms_stats.get_stats_history_availability from dual ;

GET_STATS_HISTORY_AVAILABLITY
--------------------------------------------------
17-APR-2016 03:05:26.7180000000 AM

3. exec dbms_stats.restore_schema_stats(ownername=>'SUMIT' , as_of_timestamp = > '15-May-2016 01.30.30.211212121 PM' , no_invalidate=> false ) ;