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.

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.

Richard shares these important insights on changes to dbms_stats default behavior in 10g:

“The new default value of METHOD_OPT with 10g is ‘FOR ALL COLUMNS SIZE AUTO’. This basically means that Oracle will automatically decide for us which columns need histograms and which columns don’t based on what it considers to be the distribution of data within a column and based on the “workload” associated with the table (basically are there any SQL statements running in the database referencing columns which might need histograms for those statements to be costed correctly). . .

However in environments with many tables and columns (potentially many thousands) with many users executing many different SQL statements, the ramifications of potentially suddenly having thousands of additional histograms can be disastrous.”

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