| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_lifetouch.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Lewis
Sent: Friday, June 18, 2004 4:45 PM
To: oracle-l_at_freelists.org
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/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
: 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
:
-- 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_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 Sun Jun 20 2004 - 21:21:44 CDT
 |
 |