RE: dbms_stats and incorrect statistics

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Fri, 1 Jun 2007 10:26:12 +0200
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAFB23C3E@MSXVS04.trivadis.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling Sent: Thursday, May 31, 2007 4:13 PM
To: ronnie_doggart_at_lagan.com
Cc: oracle-l_at_freelists.org
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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 01 2007 - 03:26:12 CDT