Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: DBMS_STATS [resend chomped version]

Re: DBMS_STATS [resend chomped version]

From: Connor McDonald <>
Date: Wed, 23 Jun 2004 15:55:03 +0100 (BST)
Message-ID: <>

On that note, we've had good success with a metadata table that consists of:

sample size is as you'd expect but with null=compute, and 'auto' being our own version of auto (since this thing needs to run on v8 as well as v9). We just derive used blocks from a call to dbms_space and derive an estimate from there.

The allowable frequency options are:

stale      - gather when (monitored) object goes stale (or empty)
never      - do nothing (eg mview logs, stats for segments in read-only tspaces etc)
delete     - delete stats (eg DR$.. intermedia tables)
set        - run the anonymous block (typically a set of dbms_stat.set_... calls) 
             contains in the 'clause' column
release    - gather after software release (typically a compute on static reference data)
daily      - based on last_analyzed
biweekly   - ditto
weekly     - ditto
monthly    - ditto
mmDD       - as above but on a particular day  (eg mm23)
             (used for tables that contain month end summaries etc)
yearly     - ditto
yrDDMMYYYY - as above but on a particular day  (eg yr01062004)
             (used for tables that contain year end summaries etc)
unknown - see below

The 'clause' column contains any histogram info we want to grab, or as per the 'set' command above, and there's a special row to indicate the default processing for a segment that is not in the meta-data table. "New" tables are added to the meta-data table with 'unknown' so we can see anything that's slipped through the net. Any segment with a freq setting of 'stale' gets monitoring turned on automatically.

The job runs once per night and picks up things that need to be done. Its covered just about all of our requirements so far and is relatively trivial to code up.

Hopefully this gives people ideas for their own solutions.


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"

___________________________________________________________ALL-NEW Yahoo! Messenger - sooooo many all-new ways to express yourself
Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Wed Jun 23 2004 - 09:51:47 CDT

Original text of this message