Measuring Oracle index block density with sys_op_lbid Don Burleson

The current debate over when to rebuild indexes has reached some consensus with most Oracle experts agreeing:

• Oracle 10g will soon automate index rebuilds - In an OracleWorld 2003 presentation titled Oracle Database 10 g: The Self-Managing Database by Sushil Kumar of Oracle Corporation, Kumar states that the Automatic Maintenance Tasks (AMT) Oracle10g feature will automatically detect and rebuild sub-optimal indexes.

"AWR provides the Oracle Database 10g a very good 'knowledge' of how it is being used.

By analyzing the information stored in AWR, the database can identify the need of performing routine maintenance tasks, such as optimizer statistics refresh, rebuilding indexes, etc.

The Automated Maintenance Tasks infrastructure enables the Oracle Database to automatically perform those operations."

• Sparse block matter - The most likely candidate indexes are those that experience massive delete operations, leaving "sparse" index blocks.  If these indexes are access by index fast-full-scans or multi-block index range scans, an index rebuild will likely reduce logical I/O. improving end-user response time and reducing load of the Oracle data buffer cache.
   

• Height is not an issue - Index height is never a consideration when choosing to rebuild an index.
   

• Big blocks help - Larger Index block size can reduce logical I/O and improve throughput for indexes that experience multi-block index range scans.

So, how do we measure sparse index blocks?

In a IOUG Live! 2005 paper titled "Rebuilding Indexes - Why, When, How?" - Jonathan Lewis notes that the height of an index is never a factor, and suggests the use of an undocumented function called sys_op_lbid to measure index blocks (Of course, you must always exercise caution when using any Oracle undocumented function, but sys_op_lbid appears relatively innocuous):

In Oracle 9i we can get a very nice report showing the number of index entries per used leaf block, and this could improve the precision of our investigation. We hijack the undocumented function sys_op_lbid() that appeared for use with the dbms_stats package. There are several options built into this function, but one option can be used to count the number of index entries per leaf block. Consider the following SQL statement:

select
rows_per_block, count(*) blocks
from (
select
/*+
no_expand
index_ffs(t1,t1_i1)
noparallel_index(t,t1_i1)
*/
sys_op_lbid( {NNNNN} ,'L',t1.rowid) as block_id,
count(*) as rows_per_block
from
t1
where
v1 is not null
or small_pad is not null
group by
sys_op_lbid( {NNNNN} ,'L',t1.rowid)
)
group by rows_per_block;

This easy measure of "rows per block" might be used as guideline for measuring indexes with massive delete operations.  You simple encapsulate the above SQL into a procedure (i.e. rows_per_block) and call it, passing the index name as an argument:

select

   "exec rows_per_block("||index_name||");"

from

   all_indexes;