Index Rebuild 10g

I’ve written a few notes about the pros and cons of index rebuilds in the past. A comment on Richard Foote’s blog describes a bug in 10g (reminiscent of an old index root block bug in early versions of 8i) which adds a little extra twist to the issue.

If you rebuild an index, you will see that its data_object_id (in view user_objects) no longer matches its object_id. If this is the case, apparently, Oracle keeps pinning and unpinning the buffer holding the index root block in circumstances where it would otherwise hold the pin on buffer for some time.  This means an increase in latch gets for the cache buffers chains latch covering that buffer/block.

Here’s a little result set.  I have a query with the following executon path:

----------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |   193 |    37 |
|   1 |  SORT AGGREGATE              |       |     1 |   193 |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    15 |  2775 |     2 |
|   3 |    NESTED LOOPS              |       |   225 | 43425 |    37 |
|*  4 |     TABLE ACCESS FULL        | T2    |    15 |   120 |    14 |
|*  5 |     INDEX RANGE SCAN         | T1_I1 |    15 |       |     1 |
----------------------------------------------------------------------     

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T2"."N2"=45)
   5 - access("T1"."N1"="T2"."N1")

For each row of 15 rows in table t2, we index into table t1.  After dealing with issues of block cleanout and so on, here’s the report on buffer activity from v$mystat and x$kcbsw when the index is first created:

Name                                   Value
----                                   -----
session logical reads                    108
consistent gets                          108
consistent gets from cache               108
consistent gets - examination              7
no work - consistent read gets            84
buffer is pinned count                   476
buffer is not pinned count                 7  

          Why0          Why1          Why2    Other Wait
          ----          ----          ----    ----------
             1             0             0             0 ktewh25: kteinicnt
             1             0             0             0 ktewh26: kteinpscan
            82             0             0             0 kdswh01: kdstgr
             2             0             0             0 kdswh02: kdsgrp
             1             0             0             0 kdswh05: kdsgrp
             2             0             0             0 kdiwh06: kdifbk
             2             0             0             0 kdiwh07: kdifbk
             1             0             0             0 kdiwh08: kdiixs
            15             0             0             0 kdiwh09: kdiixs
             1             0             0             0 kdiwh42: kdiixs
          ----          ----          ----    ----------
           108             0             0             0 Total: 10 rows

And this is what it looks like after the index has been rebuilt (and cleaned):

Name                                   Value
----                                   -----
session logical reads                    121
consistent gets                          121
consistent gets from cache               121
consistent gets - examination              7
no work - consistent read gets            97
buffer is pinned count                   463
buffer is not pinned count                20       

          Why0          Why1          Why2    Other Wait
          ----          ----          ----    ----------
             1             0             0             0 ktewh25: kteinicnt
             1             0             0             0 ktewh26: kteinpscan
            82             0             0             0 kdswh01: kdstgr
             2             0             0             0 kdswh02: kdsgrp
             1             0             0             0 kdswh05: kdsgrp
             2             0             0             0 kdiwh06: kdifbk
             2             0             0             0 kdiwh07: kdifbk
             1             0             0             0 kdiwh08: kdiixs
            15             0             0             0 kdiwh09: kdiixs
            14             0             0             0 kdiwh42: kdiixs
          ----          ----          ----    ----------
           121             0             0             0 Total: 10 rows

Note the way that 13 “no work - consistent read gets” have appeared, and 13 “buffer is pinned count” have become “buffer is not pinned count”.

Note also the 13 extra calls to “kdiwh42: kdiixs”, which (from the “ixs” post-fix) is an index block access - and by cross checking v$latch_children with x$bh we can see that the extra latch activity is on a latch covering the index root block.

Whoops!

By the way, this is NOT intended as any sort of proof that you should stop rebuilding all your indexes. But if you have a few critical, high performance, highly concurrent, pieces of SQL that do a lot nested loop access, and also have an unexpected latching problem on a couple of cache buffers chains child latches, then this might be your problem.

The bug is fixed in 11.1.0.6 (and 10.2.0.4 when it comes out), and there are standalone patches for 10.1.0.5 and 10.2.0.3.