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…