Restoring Previous Versions of Statistics

In some cases newly collected statistics leads to sub-optimal execution plan and you need to revert older statistics back when you had good performance.

to determine the history of statistics operations perform at a database

SQL> select start_time from DBA_OPTSTAT_OPERATIONS where operation='gather_database_stats(auto)' order by start_time desc;

START_TIME

---------------------------------------------------------------------------

03-JUN-13 12.00.02.564157 AM +03:00

31-MAY-13 12.05.51.805614 PM +03:00

31-MAY-13 08.00.03.118222 AM +03:00

27-MAY-13 12.00.09.220355 AM +03:00

24-MAY-13 12.07.34.262882 PM +03:00

24-MAY-13 08.00.08.300615 AM +03:00

20-MAY-13 12.00.03.268371 AM +03:00

17-MAY-13 12.05.10.604709 PM +03:00

17-MAY-13 08.00.03.196964 AM +03:00

13-MAY-13 12.00.08.494310 AM +03:00

10-MAY-13 12.01.03.120240 PM +03:00

10-MAY-13 08.00.03.093785 AM +03:00

06-MAY-13 12.00.02.676424 AM +03:00

13 rows selected.

Particularly you can check history statistics of a table by:

SQL>select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='ORDERS' and owner='XXXXX' order by 2 desc;
03/06/2013 12:48:33.316820 AM +03:00
03/06/2013 12:35:31.853101 AM +03:00
31/05/2013 4:09:15.617337 AM +03:00
27/05/2013 12:24:27.985570 AM +03:00
24/05/2013 4:19:12.392677 AM +03:00
20/05/2013 12:31:51.253413 AM +03:00
17/05/2013 4:15:53.924409 AM +03:00
17/05/2013 4:12:11.516809 AM +03:00
13/05/2013 12:22:08.997571 AM +03:00
10/05/2013 4:14:30.823023 AM +03:00
06/05/2013 12:20:52.902622 AM +03:00


Retrieve the actual number of records on the table which have the bad execution plan:
SQL>
----------
SQL> select count(*) from orders;
  COUNT(*)
     -------------
      16323926
let's check the last number of rows on the current statistic
SQL> select num_rows from dba_tables  where table_name='ORDERS' and owner='XXXX';
  NUM_ROWS
----------
  16295541

Now restore the statistics of the table gatherd on 31st-May 
SQL> execute dbms_stats.restore_table_stats('XXXX','ORDERS','31-May-2013 4:09:15.617337 AM +03:00');
PL/SQL procedure successfully completed.

again check the number of rows on 31st-May statistics.

SQL> select num_rows from dba_tables  where table_name='ORDERS' and owner='XXXX';
  NUM_ROWS
----------
  16045590

Finally, If you observe vacillation on the execution plan you can lock this table :

SQL> exec DBMS_STATS.LOCK_TABLE_STATS('XXXX','ORDERS');