Oracle Scratchpad

February 9, 2008

Index Rebuild 10g

Filed under: Infrastructure,Performance,Troubleshooting — Jonathan Lewis @ 9:12 am GMT Feb 9,2008

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

16 Comments »

  1. Hi,

    I have observed the same behavior in Oracle 9i Release 2 (9.2.0.8). Data object Id no longer matches with Object Id once the index is rebuild.

    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
    PL/SQL Release 9.2.0.8.0 - Production
    CORE    9.2.0.8.0       Production
    TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
    NLSRTL Version 9.2.0.8.0 - Production
    
    SQL>  create table test( a number primary key);
    
    Table created.
    
    SQL>  select index_name, table_name from user_indexes where table_name = 'TEST';
    
    INDEX_NAME                     TABLE_NAME
    ------------------------------ ------------------------------
    SYS_C003950                    TEST
    
    SQL>  select object_id, object_name, data_object_id
      2   from user_objects
      3   where object_name in ('SYS_C003950','TEST');
    
     OBJECT_ID OBJECT_NAME          DATA_OBJECT_ID
    ---------- -------------------- --------------
         35030 TEST                          35030
         35031 SYS_C003950                   35031
    
    SQL> alter index SYS_C003950 rebuild tablespace test;
    
    Index altered.
    
    SQL>  select object_id, object_name, data_object_id
      2   from user_objects
      3   where object_name in ('SYS_C003950','TEST');
    
     OBJECT_ID OBJECT_NAME          DATA_OBJECT_ID
    ---------- -------------------- --------------
         35030 TEST                          35030
         35031 SYS_C003950                   35032
    
    SQL>
    

    Comment by Asif Momen — February 10, 2008 @ 6:52 am GMT Feb 10,2008 | Reply

  2. Addming more to Asif comment, event, this behavior has been observed on tables, i.e., when a table is moved (rebuild).

    OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
    —————————————- ——————- ———- ————–
    TAB1 TABLE 139466 139466

    alter table tab1 move;

    OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
    —————————————- ——————- ———- ————–
    TAB1 TABLE 139466 139467

    Well, now the table will also have the same issues?

    Jaffar

    Comment by Syed Jaffar Hussain — February 10, 2008 @ 8:06 am GMT Feb 10,2008 | Reply

  3. There’s a bad bit of wording on my part – rebuilds, moves, truncates will always result in the data_object_id changing. I wasn’t trying to say that you see this bug “if you happen to get a change when you do an index rebuild” – you always get the change, so you always (In 10g) get the bug.

    In fact, as the special case, if you look at a table in a cluster the object_id of the table will always be different from the data_object_id of the table.

    In general, the difference allows Oracle to get read consistency right across DDL (see my note on ORA-01410 that can arise from index rebuilds).

    Comment by Jonathan Lewis — February 10, 2008 @ 9:30 am GMT Feb 10,2008 | Reply

  4. >>rebuilds, moves, truncates will always result in the data_object_id changing.

    That’s true and its a default behavior.

    We do a weekly schema reorg (using the ‘alter table move and alter index rebuild’ commands) on one of our important database (pls don’t ask me the reasons why we need to do it weekly).
    However, so for we haven’t come across of ‘Bug 6455161 Higher CPU / Higher “cache buffer chains” latch gets / Higher consistent gets after truncate/Rebuild’ because of the reorg. It could be that there are not many quries doing the NESTED LOOP on this database.

    Jaffar

    Comment by Syed Jaffar Hussain — February 10, 2008 @ 11:59 am GMT Feb 10,2008 | Reply

  5. […] Jonathan Lewis shines a light on a little bug in index rebuilds in Oracle 10g. […]

    Pingback by Log Buffer #84: a Carnival of the Vanities for DBAs — February 15, 2008 @ 6:38 pm GMT Feb 15,2008 | Reply

  6. […] A bug in 10g after index rebuilds […]

    Pingback by Index Efficiency « Oracle Scratchpad — May 14, 2008 @ 9:31 pm GMT May 14,2008 | Reply

  7. This would be Bug 6455161 ? That would impact Peoplesoft applications that use TRUNCATEs frequently on “temporary” tables that are actually permanent tables (ie, not created as Oracle GLOBAL TEMPORARY TABLEs).

    Comment by Hemant K Chitale — July 25, 2008 @ 3:22 am GMT Jul 25,2008 | Reply

  8. Jonathan,
    Can you post the code for your test case ?

    I’ve been trying in vain to reproduce the issue (looking at “consistent gets” from autotrace and from 10046 level 8 traces in different runs) but do not get a noticeable difference after a TRUNCATE and REBUILD. {I’ve taken care of delayed block cleanout as well).
    I get the same number or a slightly higher or a slightly lower number of “consistent gets” after a Table TRUNCATE and/or Index REBUILD on thee driven table in the NL join.

    Comment by Hemant K Chitale — July 29, 2008 @ 9:57 am GMT Jul 29,2008 | Reply

  9. Hermant,

    Sorry about the delay in replying to this – I think it got hidden in a backlog.

    The SQL to create the table and indexes looks like this:

    create table t1
    as
    select 
            trunc((rownum-1)/15)    n1,
            trunc((rownum-1)/15)    n2,
            rpad(rownum,180)        v1
    from
            all_objects 
    where
            rownum <= 3000
    ;
    
    
    create table t2
    as
    select 
            mod(rownum,200)         n1,
            mod(rownum,200)         n2,
            rpad(rownum,180)        v1
    from
    	all_objects 
    where
            rownum <= 3000
    ;
    
    create index t1_i1 on t1(n1);
    
    

    After you’ve created the data and collected the statistics, the SQL to query the data looks like this:

    select 
            count(n2) 
    from
            (
            select
                    /*+ ordered use_nl(t1) index(t1) */
                    t2.n1, t1.n2
            from
                    t2,t1
            where
                    t2.n2 = 45
            and     t2.n1 = t1.n1
            )
    ;
    
    
    

    Run the query a couple of times before and after the index rebuild to deal with any cleanout.

    Comment by Jonathan Lewis — September 4, 2008 @ 8:52 pm GMT Sep 4,2008 | Reply

  10. […] Index Rebuild 10g […]

    Pingback by Summary Stats « Oracle Scratchpad — April 27, 2009 @ 7:04 pm GMT Apr 27,2009 | Reply

  11. […] Index Rebuild 10g […]

    Pingback by Summary Stats « Oracle Scratchpad — April 27, 2009 @ 7:04 pm GMT Apr 27,2009 | Reply

  12. […] of Jonthan Lewis’ blog articles shows that rebuilding an index will cause its OBJECT_ID and DATA_OBJECT_ID columns to deviate from […]

    Pingback by Something is Bugging Me: V$SESSION and 10046 Trace Files Show OBJECT_ID or DATA_OBJECT_ID? « Charles Hooper's Oracle Notes — September 8, 2010 @ 10:52 pm GMT Sep 8,2010 | Reply

  13. […] So, at first sight we might decide the only way to get 400 buffer visits from the index is to have Blevel = 1, visit the root and the leaf. But that’s not the way it works (apart from a couple of versions which had a bug). […]

    Pingback by Quiz Night « Oracle Scratchpad — January 23, 2011 @ 1:41 pm GMT Jan 23,2011 | Reply

    • Jonathan,

      I have to really regret I became regular reader of your blog just two months ago !!!. What a great post, never read by me before. What a pity!!!

      Apparently, it is also giving some more answers to our last QUIZ NIGHT :)

      Comment by Pavol Babel — January 23, 2011 @ 2:16 pm GMT Jan 23,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,432 other followers