What is the Deal with AVG_LEAF_BLOCKS_PER_KEY (DBA_INDEXES) !?!

AVG_LEAF_BLOCKS_PER_KEY column in DBA_INDEXES should show the average number of leaf blocks in which each distinct value in the index appears. Common sense and Christian’s Antognini’s book Troubleshooting Oracle Performance (pg.136) suggest that the value of AVG_LEAF_BLOCKS_PER_KEY should by derived from the values of LEAF_BLOCKS and DISTINCT_KEYS .

Well, that does not appear to be the case in Oracle 11g!

This query can help you find the indexes that do not conform to the rule:

select 
       * 
from 
       dba_indexes
where 
       DISTINCT_KEYS > 0
and 
       AVG_LEAF_BLOCKS_PER_KEY  > 3*LEAF_BLOCKS/DISTINCT_KEYS
and 
       LEAF_BLOCKS > DISTINCT_KEYS

The discrepancy is certainly not a rounding error. I have an index where the value of the AVG_LEAF_BLOCKS_PER_KEY is 150 times larger than LEAF_BLOCKS/DISTINCT_KEYS.

The problem seems to be happens only when stats are gathered (DBMS_STATS) with AUTO sampling. Computing stats ( estimate_percent = NULL) resolves the issue.

Ironically, DBMS_STATS AUTO sampling delivers good estimates for LEAF_BLOCKS and DISTINCT_KEYS. If only Oracle computed AVG_LEAF_BLOCKS_PER_KEY by using LEAF_BLOCKS and DISTINCT_KEYS, everything would have been great…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: