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