Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: segment monitoring, stats, histograms

Re: segment monitoring, stats, histograms

From: Connor McDonald <>
Date: Fri, 4 Feb 2005 00:58:36 +0000 (GMT)
Message-ID: <>

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:

  1. 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
  2. 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
  3. 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).


> What is the current state of the art WRT CBO best practices?
> I'm working on 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
> --

Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions" ISBN: 1590592174 web:

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.
Received on Thu Feb 03 2005 - 20:01:15 CST

Original text of this message