TIP 72# : Restore old statistics !

I have a client which called me yesterday and complained that users were experiencing performance degradation while there was no major change on database.
After some investigations, I found that auto 10g stats gathering is on and from DBA_OPTSTAT_OPERATIONS and DBA_TAB_STATS_HISTORY I found that last stats gathering was close to the time that users started complaining about performance.So as my first guess I pointed to the new stats as culprit. To prove this guess, I was Lucky that database was 10g so that old version of stats are saved automatically.
I went with the idea that If new stats resulted to suboptimal execution plan, old stats could be restored to fix the issue.

To restore statistics, I ran exec dbms_stats.restore_table_stats({owner} ,{table name}, {timestamp}); which replaced old stats with new stats.
After restoring old stats, performance problem was resolved.
Also in order to keep stats on this table intact, I lock stats on that specific table for a while.



1 comment:

Haris said...

Thanks for the quick and easy answer. I don't like too "wordy" blog and this was perfect.