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]

Date: Sun, 20 Jun 2004 21:20:41 -0500
Message-ID: <0186754BC82DD511B5C600B0D0AAC4D607B0084A@EXCHMN3>


   Very provocative ideas.
1. I'm assuming you are referring to the AUTO sample size when you say don't let the database work out a sample size? Does anybody know how that works? I hate to trust something if I have no idea how it works.

2. The study of statistics has a branch related to sample size. Pollsters use that to figure out how many random samples can provide a valid estimate. I'm searching for my old college statistics textbook right now.

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 of next analyze, etc. This would ensure each table was analyzed appropriately.

4. My boss heard some Oracle expert say that 30% was the best sample size. Does anyone have an idea of the source?

5. Thanks for your common sense statement that if the machine has excess capacity it doesn't do any damage to analyze to the extreme. And thanks always for your clear insights. Just reading your postings is a great education.

Dennis Williams
Lifetouch, Inc.

-----Original Message-----
[]On Behalf Of Jonathan Lewis Sent: Friday, June 18, 2004 4:45 PM
Subject: Re: DBMS_STATS [resend chomped version]

One - don't let the database work out a sample size, at best it will waste time you don't have.

Two - don't let the database decide which columns are skewed, at best it will generate far too many histograms.

Three - (for Jared) if you analyze for all indexed columns you've almost certainly done it wrong: some of your unindexed columns may need histograms, most of your indexed ones won't.

On the other hand - if you have several hours of free time for analyzing, and an overpowered machine, you don't often do much damage by analyzing to extremes.

Optimum use of stats:

    Most tables need only a small percentage estimate

    A few columns (time or sequence-based) need very regular correction

    A few columns need histograms - designed to highlight the skewed     data pattern.

    Small tables may as well have a compute - as a small     error in the cardinality of the data set from a small table     can produce a significant percentage error in cost estimate     as it cascades through a plan - and small tables can be     analyzed very quickly.          


Jonathan Lewis The Co-operative Oracle Users' FAQ Optimising Oracle Seminar - schedule updated May 1st

: Regarding #1:
: ( Solaris 9)
: We have a recommendation from the sw vendor to compute
: with histograms using
: exec dbms_stats.gather_schema_stats(ownname =>
: 'SCOTT', estimate_percent =>
: dbms_stats.auto_sample_size, method_opt => 'for all
: columns size skewonly', cascade => true);
: as an initial method to gather the stats. I did a bit
: of looking and a bit of testing, and it looks good to
: me. Down side: it did take several hours to gather
: the initial set of stats.
: What are other folks doing with histograms??
: Barb

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
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 Sun Jun 20 2004 - 21:21:44 CDT

Original text of this message