The reason for mentioning this particular posting is not specifically its reference to Statspack, it’s for the throwaway comment that Doug uses to explain how he was rapidly able to address the problem highlighted by Statspack:
Better still, because we save all of our object statistics for the CBO for at least 30 days, we could see which statistics had changed and investigate why Oracle was picking a different execution plan.
It is a little known fact (first brought to my attention in a presentation by Tim Gorman) that it is very easy to save the existing statistics when collecting new statistics. All you need to do is create a ‘stats table’ and then reference it – with some sort of identifier for the statistics you are saving – in the call to gather statistics. For example:
rem rem Create a stats table rem begin dbms_stats.create_stat_table( ownname => user, stattab => 'JPL_STATS', tblspace => 'STATS_TS' ); end; / rem rem Extract referencing the stats table rem Note the last three parameters rem begin dbms_stats.gather_table_stats( ownname => user, tabname => 'TABLE_X', cascade => true, estimate_percent => 0.01, block_sample => true, granularity => 'default', method_opt => 'for all columns size 1', statid => 'JPL_' || to_char(sysdate,'yyyymmdd'), stattab => 'JPL_STATS', statown => user ); end;
In fact, this technique is also mentioned and demonstrated on another of Doug Burns’ postings, So I’ll let you read that rather than creating a full example.
Footnote: 10g automatically saves the old statistics into a set of built-in stats history tables (with names like ‘%OPTSTAT%’) whenever you generate new statistics with the dbms_stats package (but misses some of them if you are still using the analyze command). This highlights an important detail to check when you upgrade. If you are already using the older ‘save stats’ method will you now be saving two sets of stats every time you collect new stats ? If so, you may want to make a strategic change to your methods – bearing in mind that the stats saved automatically in 10g will be discarded after 31 days because of the default setting of the ‘stats_retention’ parameter, which can be modified by a call to dbms_stats.alter_stats_history_retention().