RE: DBMS_STATS [resend chomped version]

Jonathan
   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
DBA
Lifetouch, Inc.
dwilliams@xxxxxxxxxxxxx 


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis
Sent: Friday, June 18, 2004 4:45 PM
To: oracle-l@xxxxxxxxxxxxx
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.

    

    
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


----- Original Message ----- 
From: "Barbara Baker" <barbarabbaker@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, June 16, 2004 11:12 PM
Subject: RE: DBMS_STATS [resend chomped version]


: Regarding #1:
: (9.2.0.4 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: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
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
-----------------------------------------------------------------
----------------------------------------------------------------
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: