Re: gather stats
Date: Mon, 9 Nov 2015 18:23:46 +0000
Message-ID: <CABe10sY6Ra8hfqwqE8Ws_sgZ5kU0uNzrS=jFzL07xxOBadTP9A_at_mail.gmail.com>
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Nov 09 2015 - 19:23:46 CET