Query Execution Plan Changing due to New Statistics

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-12 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.