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

On Dec 6, 7:08 am, nicola.far..._at_info-line.it wrote:
> 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

Sounds like statistics are missing for the index.

Maybe different procedures in place in the different sites? Maybe a DELETE_INDEX_STATS was run?

When you say "Statistics are up-to-date?" ... what do you base that on? Received on Thu Dec 06 2007 - 07:40:44 CST