The query below can be used to obtain the number of rows per index leaf block.
You will need to know the index name, the table name that the index references, the pk column in the index and the object id for the index:
WHERE object_name = 'MGMT_VERSIONS_PK';
SELECT rows_per_block, count(*) blocks
SELECT /*+ cursor_sharing_exact
sys_op_lbid(349440, -- << INDEX OBJECT ID HERE
FROM MGMT_VERSIONS -- << TABLE NAME HERE
WHERE COMPONENT_NAME IS NOT NULL -- << INDEX COL NAME HERE
GROUP BY sys_op_lbid(349440, -- << INDEX OBJECT ID HERE
GROUP BY rows_per_block;
This is useful for determining sparse index blocks:
It could be combined with this script (https://jonathanlewis.wordpress.com/segment-scans/) which helps to detect full table scans and index fast full scans:
statistic_name = 'segment scans'
and value != 0
order by owner, value;
(When I get time, I’ll combine it and post it here).
Running the first SQL statement, then checking if the table has “high” segment scans would then give a good indication if the index is used frequently for large multi-block operations and is very sparse (block wise) and potentially up for a re-build.