Just for clarification, the last mentioned option (separate call with just the
columns specified that should get histograms) can be combined into a single
DBMS_STATS call, so the following is a perfectly valid METHOD_OPT expression:
"FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 COLA,COLB"
This way COLA and COLB get a histogram (either a frequency or height-balanced
in 11.2 depending on the number of distinct column values) but all other
columns don't get one - and it uses the tiny sample size Jonathan mentioned
(typically 5500 rows), and this is all done in a single call, avoiding the
second call to DBMS_STATS that will collect table level stats again (avg row
size, number of rows etc.)
To the OP: Don't use FOR ALL COLUMNS SIZE SKEWONLY, except you're sure this is
a good idea, as it generates histograms on any column that qualifies for a
histogram based on value distribution although it might not be used in any
predicate so you accumulate potentially a huge number of useless histograms in
your data dictionary, also Oracle has to generate a histogram on every column
to find out whether it needs to keep it or not, so it also consumes more time
and resources at gather stats time.
Don't use ESTIMATE_PERCENT => 2 unless you're sure this is a good idea, from 11.1 on you
should use NULL as ESTIMATE_PERCENT which gives accurate statistics (100% sample) but takes
only a fraction of the time of the old "100" percent ((not so) new approximate NDV
algorithm avoiding sorts)
Of course, keep in mind with all these recommendations - if you change the way
stats are gathered you potentially end up with plan changes. Even if the
changed method of gathering statistics in principle should provide more
accurate statistics you still might end up with plan regressions hence this
needs to be tested and treated carefully.
Randolf
If you then think these two columns need a histogram you either live with an
expensive insert and histogram generation, or you write some SQL that creates
the histograms from scratch (or, if you're amazingly lucky) use method_opt in a
separate call to specify just the columns that you want to have histograms and
live with the tiny sample that Oracle will use for generating a histogram with
auto_sample_size.
