Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

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;
Sparse index nodes result from bulk row delete operations and use can use STATSPACK and AWR to see if an index experiences index range scans or fast full scans, using the plan9i script or an Oracle10g AWR query.
 

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational