Rolling Back Statistics

Submitted by dave on Fri, 09/12/2014 - 16:42

If your statistics get corrupted or mismanaged for any reason you may be able to roll back to a previous version of your stats. Here is how you can do it for 1 schema:

1) Backup the current stats for your schema

BEGIN
  dbms_stats.export_schema_stats( ownname=>'[SCHEMA_NAME]'
                                , stattab=>'STAT_BACKUP_[SCHEMA_NAME]_[DATE]'
                                , statown => 'SYS'
                                , statid=>'CURRENT_STATS');
END;
/

Note: You can watch progress of this operation by watching long operations view (SQL is below).

2) Delete the schema's stats
This is for safety. If you happened to have new stats generated recently these will still be in play unless you delete them to start with a clean slate. 

BEGIN
  DBMS_STATS.DELETE_SCHEMA_STATS (
    ownname          => '[SCHEMA_NAME]'
  );
  COMMIT;
END;
/

Note: You can watch progress of this operation by watching long operations view (SQL is below).

3) Load stats from a previous point in time
Substitute appropriate date in the following to_timestamp() function call. 

BEGIN
  DBMS_STATS.RESTORE_SCHEMA_STATS( 
   ownname => '[SCHEMA_NAME]', 
   as_of_timestamp  =>to_timestamp('04-SEP-2014 07.00.00.000000 PM','dd-mon-yyyy hh.mi.ss.ff am') );
commit;
END;
/

Note: You can watch progress of this operation by watching long operations view (SQL is below).

4) Flush shared pool
Run the following on all instances:

ALTER SYSTEM FLUSH SHARED POOL;

Your reverted stats should now be in play for all newly issued queries.

Reference : Long operations view

SELECT ROUND(sofar/totalwork*100,2), v$session_longops.*
  FROM v$session_longops
 WHERE sofar <> totalwork
 ORDER BY target, sid;