Re: DBMS_STATS [resend chomped version]

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 18 Jun 2004 22:45:00 +0100
Message-ID: <011001c4557d$8390bbb0$7102a8c0@Primary>

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_at_yahoo.com> To: <oracle-l_at_freelists.org> 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_at_freelists.org 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
-----------------------------------------------------------------

Received on Fri Jun 18 2004 - 16:42:10 CDT