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 


 

 

 


 

 

 

 

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 (10.1.0.2 through 10.2.0.3), 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 10.2.0.3 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 10.1.0.2 through 10.2.0.3.

 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. ***

Workaround:

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