I imagine you'll get a number of contradictory answers to this one. I'd say
that *principles* that I'd recommend are
Use AUTO SAMPLE SIZE - this will be both quicker and more accurate on at
least non partitioned tables
Determine a strategy involving incremental stats for partitioned objects.
Allow a significant amount of time for testing.
Avoid histograms whenever *possible*. Histograms by design introduce plan
instability. When it works it's great. When it doesn't however...! Note
though if you follow this advice you are working against the strategic
direction of the Oracle optimizer developers.
Be prepared to accept good enough stats and use plan stability/hints on a
documented case by case exception basis. Getting the *perfect* strategy is
beyond you - if it wasn't almost certainly auto everything would be a short
cut to a great result because the optimizer would be able to get there as
well.
All,
What is the recommended way of gathering stats on 11gR2 database ( 2.5
TB) . If i keep estimate percent 10,15 then some of the table are taking
hours and hours .
Please let me know .
Thanks in advance.
Kart.
