dbms_stats and incorrect statistics

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.

 

The problem is compounded when we switch to estimate_percent as shown
below,

 

Estimate_percent=>30 

 

TABLE_NAME                         COLUMN_NAME
DATA_TYPE            NUM_DISTINCT      SAMPLE_SIZE

LGNCC_ESCRULE                  ESCRULESTATUS                  NUMBER
4                  4468      

LGNCC_ESCRULE                  DATEFIRED                            DATE
2267                  2664

 

Estimate_percent=>15

 

TABLE_NAME                         COLUMN_NAME
DATA_TYPE            NUM_DISTINCT      SAMPLE_SIZE

LGNCC_ESCRULE                  ESCRULESTATUS                  NUMBER
4                    2838      

LGNCC_ESCRULE                  DATEFIRED                            DATE
0             

 

The sample_size in the line above is blank as reported from
dba_tab_cols.

 

Has anyone seen anything like this before ?

 

 

Ronnie Doggart

 


The information in this message is confidential and may be legally 
privileged. It is intended solely for the addressee. Access to this 
message by anyone else is unauthorized. If you are not the intended 
recipient, any disclosure, copying, or distribution of the message, or any 
action or omission taken by you in reliance on it, is prohibited and may 
be unlawful. Please immediately contact the sender if you have received 
this message in error.

Other related posts: