Re: DBMS_STATS [resend chomped version]

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Wed, 23 Jun 2004 15:55:03 +0100 (BST)
Message-ID: <20040623145503.60510.qmail@web25205.mail.ukl.yahoo.com>

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote: >
>
> > 3. I like your idea of creating a table to hold the sample size for each
> > table in the schema. Other columns could hold the reanalyze interval, date
>

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

segment owner/name
sample size
freq
clause

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.

hth
connor

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"

___________________________________________________________ALL-NEW Yahoo! Messenger - sooooo many all-new ways to express yourself http://uk.messenger.yahoo.com
----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jun 23 2004 - 09:51:47 CDT