Backing up Oracle optimizer statistics


Oracle 10g has some neat features for keeping track of old statistics. Pre-10g It used to be important to backup your statistics before you analyzed a table just in case your plans went crazy. Now Oracle does this for you automatically. There is a great post on Doug’s Oracle Blog that talks about this with some examples. So now one can analyze as needed without fear of not being able to roll back the statistics to the previous value. In a crisis, it might be something to check to see if analyze recently ran and now plans are bad by selecting from tab_stats_history. Then simply back out the stats with dbms_stats.restore_schema_stats to revert back to a known good statistics state. Be sure to check dbms_stats.get_stats_history_retention and make sure you are keeping a long enough record of stats, you can adjust with dbms_stats.alter_stats_history_retention as needed to keep yourself sleeping at night.