Donald Burleson Click here for more Oracle News Headlines

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