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.

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