FLEXCUBE, gather statistics without using histograms.
                                                 Last update (2012-06-08 23:05:54)
                                                                                                                   Date added (2010-07-09 15:37:25)

Posted by: George Kaparelis

The new default value of METHOD_OPT with Oracle 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 values 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).

This sounds like an "ideal scenario", just let Oracle work it out for us. However, the problem is that Oracle in many cases doesn't do a particularly good job at determining when it should generate a histogram and when it shouldn't. In fact, the likelihood is that Oracle will actually generate many many many unnecessary histograms while still missing out on some columns that should have them.

In environments with few tables and with few users executing few distinct SQL statements, the impact of some unnecessary histograms may be minimal. However in environments with many tables and columns (potentially many thousands) with many users executing many different SQL statements, for example ORACLE FLEXCUBE, the ramifications of potentially suddenly having thousands of additional histograms can be disastrous.

Note also that by having a histogram, Oracle changes the manner in which the DENSITY statistic for a column is calculated (as stored in DBA_TAB_COLUMNS). This is often used by Oracle to determine the selectivity of predicates so the impact of suddenly having additional unnecessary histograms can be wider and more significant than one might initially imagine.

Of course, the impact on the shared_pool and the row_cache and it's associated latches in particular can be extremely ugly indeed if suddenly Oracle with FLEXCUBE had to deal with thousands of new histograms when parsing statements!
exec dbms_stats.gather_schema_stats(ownname=>'FCC',