Re: Determine wich procedure is used to update optimiser statistics

There is a small trick to find out.
DBMS_STATS and ANALYZE command show different values for AVG_ROW_LEN and EMPTY_BLOCKS columns in DBA_TABLES.

for ex - Create table X as select * from all_objects;

exec dbms_stats.gather_table_stats('SCOTT','X');  

                         Avg
                         Row
                         Len       No Of           Empty
Owner           Table    gth        Rows   Blocks Blocks


--------------- ----- ------ ----------- -------- ------

SCOTT           X         92       30987      422      0

 analyze table x compute statistics;

                         Avg
                         Row
                         Len       No Of           Empty
Owner           Table    gth        Rows   Blocks Blocks


--------------- ----- ------ ----------- -------- ------

SCOTT           X         96       30987      422     89

So to find out, for a table you generate the STATS again using ANALYZE.
If you get the same value for AVG_ROW_LEN and EMPTY_BLOCKS that means you have used ANALYZE presiously, else you may have used DBMS_STATS. DBMS_UTILITY is just a wrapper which uses ANALYZE. Whenever possible, DBMS_STATS calls a parallel query to gather statistics with the specified degree of parallelism, Otherwise, it calls a serial query or the ANALYZE statement. With 8.1;x DBMS_STATS uses recursive ANALYZE to gather statistics on All Index Statistics,All Histograms.
With 9.2.x DBMS_STATS uses recursive ANALYZE to gather statistics only on
CLUSTER INDEX, DOMAIN INDEX, JOIN INDEX regards
Srivenu Received on Thu Feb 26 2004 - 02:10:51 CST