Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Tuesday, November 01, 2011

Oracle Index Rows Per Leaf Block

Print Friendly and PDF
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:
http://www.dba-oracle.com/t_sys_op_lbid_index_node_density.htm

It could be combined with this script (http://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.

No comments: