RE: dbms_stats and incorrect statistics
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <ronnie_doggart@xxxxxxxxx>
- Date: Fri, 1 Jun 2007 10:26:12 +0200
Ronnie
I fully agree with Wolfgang. This is the usual reason.
Let me add that the problem you see with estimate_percent=>15 is due to
the inability of the SAMPLE clause to deal with non-uniform data
distribution. Such problems were quite common up to 9iR2. Fortunately in
10g it is much better. But of course as any sampling, it's just a
sampling and you will always miss some data.
HTH
Chris
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Wolfgang Breitling
Sent: Thursday, May 31, 2007 4:13 PM
To: ronnie_doggart@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: dbms_stats and incorrect statistics
I bet DATEFIRED has 5842 NULLs - or had at the time you gathered the
stats.
At 01:12 AM 5/31/2007, Ronnie Doggart wrote:
Hi,
Oracle 9.2.0.8 on Oracle Enterprise Linux
We looking at reducing the amount of time taken to gather statistics on
our production database. Initially we where using
dbms_stats.gather_schema_stats with compute and cascade=>true. We want
to use an estimate on the tables and compute on the indexes so we split
the analysis up. However during testing we noticed that some tables had
stats that where inconsistent. For instance using
dbms_stats.gather_table_stats with compute the number of rows reported
as sampled varied on a column per column basis in the same table e.g
TABLE_NAME COLUMN_NAME
DATA_TYPE NUM_DISTINCT SAMPLE_SIZE
LGNCC_ESCRULE ESCRULESTATUS NUMBER
4 14563
LGNCC_ESCRULE DATEFIRED DATE
4896 8721
Does a compute not read all the rows in a table for each column, the
table above has 14563 rows.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com <http://www.centrexcc.com/>
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
Other related posts:
