How is possible that avg_data_blocks_per_key = 0 even with fresh statistics ?

Hi all,

I am trying to tuning a query on one of our customers site. The query is very complex, so it is difficult to humanly "see" fallacies in the plan. I tried comparing real with estimated cardinalities to find critical points.
Now I have had the idea to check the same query on another customer which has the same Oracle version (9206 but on win32). At this second site the query performance are good. The plan is similar but with some differences. The second site uses more TABLE ACCESS BY INDEX ROWID where the first one use FULL ACCESS SCAN.
I choosed an index and compared the statistics between the two. The query I used is:

SQL> select u.clustering_factor, u.avg_data_blocks_per_key, u.num_rows, u.distinct_keys
  2 from user_indexes u
  3 where u.index_name = 'PK_T_DIPARTIMENTI_BUDGET'   4 ;

These are the results:

site #1 (the slow one)

CLUSTERING_FACTOR AVG_DATA_BLOCKS_PER_KEY NUM_ROWS DISTINCT_KEYS
----------------- ----------------------- -------- -------------

                1                       1       31 31

site #2 (the good one)

CLUSTERING_FACTOR AVG_DATA_BLOCKS_PER_KEY NUM_ROWS DISTINCT_KEYS
----------------- ----------------------- -------- -------------

                0                       0        0 0

An important difference between these two system is also block size

site #1 has 8192 (with DBMBRC=32) while
site #2 has 4096 with DBMRC=16

But I really don't understand
how is it possible that cf, avg_data_blocks_per_key etc.. are zero for this index ? Statistics are up-to-date ...?

Any help/tips will be very appreciated!
Thanks
Bye
Nicola Received on Thu Dec 06 2007 - 06:08:24 CST