Re: DBMS_STATS [resend chomped version]
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 18 Jun 2004 22:45:00 +0100
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
