Re: DBMS_STATS [resend chomped version]
- From: Connor McDonald <hamcdc@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 23 Jun 2004 15:55:03 +0100 (BST)
--- Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> 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@xxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
