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

Sorry just not enough caffeine in me yet. Maybe the table at one site was truncated/empty when the stats were gathered?

That way the stats "can be current" yet still useless and not helpful to the optimizer after data has been placed into the table. Received on Thu Dec 06 2007 - 07:45:11 CST