About Stats

You might encounter scenarios where your queries execution plan changed. Eg : On Monday your query was running in less than a minute and on Wednesday your query started running for an hour (The query was exactly the same, query criteria (Including bind variables) were the same and the table data has not changed too much). One of the reasons for the query performing badly now, could be that the execution plan has changed.
One of the reasons that the execution plan has changed, could be that the statistics on the table has changed (Between monday and wednesday, gather stats could have run on the table). Wont it be nice if you were able to restore the old stats on the table, which was helping the query run faster ?
There might be a way in Oracle 10g and Higher.
Whenever oracle collects stats on a table using gather_table_stats, oracle stores away the existing stats on the table before updating the table with the newly collected stats. So there is also a mechanism to restore this stats that oracle backed up.
We can use the following steps to restore this stats (Which was good)
    * Let us say that SCOTT.DEPT is the table in question
    * First of all, find out, when the table stats were modified
      select stats_update_time from user_tab_stats_history where table_name = ‘DEPT’;
    * Create a stats table in the schema of the table owner (This will serve as the temporary holder of the current statistics, if you ever have to restore this)
      exec dbms_stats.create_stat_table ( -
      ‘SCOTT’, -
      ’stattab_new’);
    * Export the existing table statistics to this temporary table (stattab_new)
      exec dbms_stats.export_table_stats ( -
      ‘SCOTT’, -
      ‘DEPT’, -
      null, -
      ’stattab_new’, -
      null, -
      true, -
      ‘SCOTT’);
    * Restore the old stats, which used to give you a better execution plan
      exec dbms_stats.restore_table_stats ( -
      ‘SCOTT’, -
      ‘DEPT’, -
      ‘21-JAN-09 11.00.00.000000 AM -05:00');
The third argument you give is the time upto which you want to restore the stats. Once you query the table user_tab_stats_history, determine a time when the stats would have been the good statistics (So pick a time in between the analyze which had the good stats and the analyze that had the bad stats). Use that time as the third argument.
Now if you get the query to reparse it should pick up the updated good statistics, use the good execution plan and execute with good performance.
Note : Try and perfect this technique on test databases before running this on production.

===========
Statistics History
One of the complications that can occur during optimizer statistics collection is changed execution plans—that is, the old optimization works fine until the statistics are collected, but thereafter, the queries suddenly go awry due to bad plans generated by the newly collected statistics. This is a not infrequent problem.
To protect against such mishaps, the statistics collection saves the present statistics before gathering the new ones. In the event of a problem, you can always go back to the old statistics, or at least examine the differences between them to get a handle on the problem.
For example, let's imagine that at 10:00PM on May 31 the statistics collection job on the table REVENUE is run, and that subsequently the queries perform badly. The old statistics are saved by Oracle, which you can retrieve by issuing:
begin
   dbms_stats.restore_table_stats (
      'ARUP',
      'REVENUE',
      '31-MAY-04 10.00.00.000000000 PM -04:00');
end;
This command restores the statistics as of 10:00PM of May 31, given in the TIMESTAMP datatype. You just immediately undid the changes made by the new statistics gathering program.
The length of the period that you can restore is determined by the retention parameter. To check the current retention, use the query:
SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
                         31
which in this case shows that 31 days worth of statistics can be saved but not guaranteed. To discover the exact time and date to which the statistics extend, simply use the query:
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------
17-MAY-04 03.21.33.594053000 PM -04:00
which reveals that the oldest available statistics date to 3:21AM on May 17.
You can set the retention period to a different value by executing a built-in function. For example, to set it to 45 days, issue:
execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45)