Important changes in 10g statistics collection

Each new release of Oracle brings enhancements, and 10g has radically changed some important defaults for Oracle statistics collection (using dbms_stats). The one-size-fits-all approach is a great baseline, but the automatic 10g statistics collection may not be just-right for your database.

In my notes on Oracle 10g migration upgrade performance I note that Oracle 10g does automatic statistics collection and your original customized dbms_stats job (with your customized parameters) will be overlaid.

You may also see a statistics deficiency (i.e. not enough histograms) causing performance issues. Re-analyze object statistics using dbms_stats and make sure that you collect system statistics.

execute dbms_stats.gather_system_stats('start');
-- wait an hour or so
execute dbms_stats.gather_system_stats('stop');

With Oracle Database 10g, there are some new arguments available for the dbms_stats package subprograms. Those changed 10g dbms_stats parameters are granularity and degree.

There are also cases where you need to disable the automatic statistics collection on Oracle10g because of the high expense of the 10g default arguments for dbms_stats method_opt which is "for all columns size auto". This default causes lots of work as Oracle examines all columns to determine if they may benefit from having histograms.

In sum, 10g SQL optimization involves a perpetual trade-off between sub-optimal plans and the un-needed overhead of superfluous or un-referenced histograms.

An exploration of "intelligent histogram creation", is needed, a method that uses AWR to correlate the SQL to the objects, avoiding histograms that are never used, and develop a method to only create histograms that "make a difference".

Also see: Oracle 11g changes to dbms_stats

��