Re: segment monitoring, stats, histograms

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Fri, 4 Feb 2005 00:58:36 +0000 (GMT)
Message-ID: <20050204005836.30200.qmail@web86901.mail.ukl.yahoo.com>

My $0.02 worth based on experience at a current site

I'd opt explicit dbms_stats facilities on some/most tables, with monitoring and gather_stale as the fallback option for those without any specific requirement. My main reasons for not relying solely on monitoring and/or auto sample size are:

I've found that auto histogramming gives a LOT of histograms, many of which upon further inspection were not actually required. COL_USAGE$ is nice to have though to see roughly how things are being accessed though
I've been very disappointed with auto-sample size. It runs more expensive (because its implemented as repeated iterations with increasing sample sizes). Also, I've found it be poor with skewed column data - the resulting sample sizes are not large enough
I've been bitten a few times with sampling on indexes - I can't remember the specifics but it tends to get clustering factor and distinct keys a little wrong. So I generally run without cascade and always do 100% samples if possible on the indexes.

Our process automatically turns on monitoring for any tables not covered by special requirements, and runs a gather stale at regular intervals (the fewer the better - we try to minimise the amount of statistics runs we do - generally once per release). We've detected no overheads with monitoring (although admittedly we haven't really spent a great deal of time looking for them)

Other small exceptions - we've got dynamic sampling set to 2 (because the app uses temp tables heavily) and we've stats on the dictionary (app does lots of parsing, and having dyn sampling set to 2 means dictionary queries also invoked dyn sampling).

hth
connor

Jeremiah Wilton <jeremiah_at_ora-600.net> wrote:

> What is the current state of the art WRT CBO best practices?
>
> I'm working on 9.2.0.4 and considering the 'automated statistics gatherin=
> g' approach. This involves turning on monitoring for any and all tables =
> that need to ever have stats updated, then periodically running dbms_stat=
> s in gather_stale mode.
>
> How is this working for people? Does monitoring impact DML operations, a=
> nd if so, how much?
>
> Does this approach make any kind of intelligent decisions about sample si=
> zes and block sampling?
>
> When histograms are present, does this approach always/never/sometimes re=
> generate the histogram with the correct number of buckets?
>
> Does it seem to reliably choose the correct tables to analyze?
>
> --
> Jeremiah Wilton
> ORA-600 Consulting
> http://www.ora-600.net
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions" ISBN: 1590592174 web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 03 2005 - 20:01:15 CST