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
-----------------------------------------------------------------

Other related posts: