RE: DBMS_STATS [resend chomped version]
- From: DENNIS WILLIAMS <DWILLIAMS@xxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 20 Jun 2004 21:20:41 -0500
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 //www.freelists.org/archives/oracle-l/
FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
