Oracle Training Oracle Support Development Oracle Apps

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Important Oracle 10g bug in index rebuilding

It is noted here that a MOSC note 6455161.8 and Bug 6455161 are affecting index rebuilding performance in Oracle 10g ( through, causing excessive logical I/O (consistent gets). 

Index rebuilding has been proven to greatly improve performance under certain cases, and see my notes on the latest consensus on index rebuilding to understand the benefits of index rebuilding.

Jack notes that the workaround for the index rebuilding bug is to drop and recreate all indexes which OBJECT_ID is not equal to DATA_OBJECT_ID to synchronize the two values.  Jack goes on to describe his issue:

"We hit a significant bug on with ALTER INDEX REBUILD. See Bug 6455161 “Higher CPU / Higher “cache buffer chains” latch gets / Higher “consistent gets” after truncate/Rebuild”

Note: 6455161.8 (21-DEC-2007), which apparently it affects through

 Bug Description

The issue is that the index root block is pinned and unpinned for each key lookup during a Nested Loop on Indexes that have OBJECT_ID DATA_OBJECT_ID (as viewed in DBA_OBJECTS).

Each pin/unpin operation obtains the “cache buffer chains” latch, uses extra cpu and increases the “CONSISTENT GETS” statistic.

At the individual statement level queries doing Nested Loops with

Indexes lookups may do more “cache buffer chains” latch gets and have a larger than expected “CONSISTENT GETS” statistic (”query” column on tkprof).

At the instance level it is possible to get some contention on the

“cache buffer chains” latch and some increase in the CPU utilization.

The DATA_OBJECT_ID for an index changes if an ALTER INDEX REBUILD or TRUNCATE TABLE operation has been performed on the index.

To find a list of affected objects:

select owner||’.'||Object_name||’.'||subobject_name
from dba_objects
where object_iddata_object_id
and object_type like ‘INDEX%’
order by owner,object_name,subobject_name;


*** that this only affects use of such indexes in a NESTED LOOPS operation. ***


Drop and Recreate all indexes which OBJECT_ID [is not equal to] DATA_OBJECT_ID. This synchronizes the two values.

(Note that it is not sufficient to REBUILD the index, it must be explicitly dropped then recreated)

The bug was quite nasty in our case. The work-around to Drop and Recreate indexes worked like a charm, but wasn’t how I originally planned to spend that Saturday.  After just going through a major tuning exercise with ALTER INDEX REBUILD, which was actually an un-tuning exercise."

Related index rebuilding notes

See my related notes on Oracle index rebuilding:



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