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:
SELECT object_id
from user_objects
WHERE object_name = 'MGMT_VERSIONS_PK';
SELECT rows_per_block, count(*) blocks
FROM (
SELECT /*+ cursor_sharing_exact
dynamic_sampling(0)
no_monitoring
no_expand
index_ffs(serv_inst,ix_serv_inst)
noparallel_index(serv_inst,ix_serv_inst)
*/
sys_op_lbid(349440, -- << INDEX OBJECT ID HERE
'L',rowid) block_id,
count(*) rows_per_block
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
'L',rowid)
)
GROUP BY rows_per_block;
This is useful for determining sparse index blocks:
https://www.dba-oracle.com/t_sys_op_lbid_index_node_density.htm
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:
select
owner,
object_type,
object_name,
obj#,
subobject_name,
tablespace_name,
value scans
from
V$segment_statistics
where
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.
