3 Replies Latest reply: Oct 4, 2010 10:34 AM by Taral RSS

    Index Build 11g

    Taral
      Hi Guru,

      Here is the small test case.I don't know it's strange behavior or it's obvious one.

      VERSION
      ----------
      11.2.0.1.0
      First Test
      ===========
      drop table test2 purge;
      create table test2 nologging as select * from dba_objects;
      insert into test2  select * from test2;
      insert into test2  select * from test2;
      insert into test2  select * from test2;
      commit;
      exec dbms_stats.gather_table_stats(user,'TEST2');
      
      TDESAI_DBA@hadwts01-db > select count(*) from test2;
      
        COUNT(*)
      ----------
         7136168
      
      drop table test1 purge;
      create table test1 nologging as select * from dba_objects;
      insert /*+ append */ into test1 select * from test1;
      commit;
      insert /*+ append */ into test1 select * from test1;
      commit;
      insert /*+ append */ into test1 select * from test1;
      commit;
      insert /*+ append */ into test1 select * from test1;
      commit;
      exec dbms_stats.gather_table_stats(user,'TEST1');
      create index test1_idx on test1(object_id);
      alter index test1_idx unusable;
      select count(*) from test1;
      alter system flush buffer_cache;
      insert /*+ append */ into test1 select * from test2;
      commit;
      --Now start Tracing
      alter index test1_idx rebuild;
      --Stop Tracing
      
      Second test
      -----------------
      Just Change 
      alter index test1_idx rebuild online;
      
      Test 1
      --------
      
      alter index test1_idx rebuild
      
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        1      0.00       0.00          0          3          1           0
      Execute      1     55.97      62.98     205007     205122      26278           0
      Fetch        0      0.00       0.00          0          0          0           0
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total        2     55.97      62.98     205007     205125      26279           0
      
      Misses in library cache during parse: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 308
      
      Rows     Row Source Operation
      -------  ---------------------------------------------------
            1  INDEX BUILD NON UNIQUE TEST1_IDX (cr=208067 pr=205011 pw=24993 time=0 us)(object id 0)
      21408536   SORT CREATE INDEX (cr=205023 pr=205003 pw=0 time=10623558 us)
      21408536    TABLE ACCESS FULL TEST1 (cr=205023 pr=205003 pw=0 time=16969608 us cost=45401 size=85634208 card=14272368)
      
      
      Elapsed times include waiting on following events:
        Event waited on                             Times   Max. Wait  Total Waited
        ----------------------------------------   Waited  ----------  ------------
        reliable message                                1        0.00          0.00
        enq: KO - fast object checkpoint                1        0.00          0.00
        direct path read                             2536        0.06          8.77
        db file sequential read                         4        0.00          0.00
        Disk file operations I/O                        2        0.00          0.00
        control file sequential read                   95        0.00          0.02
        control file parallel write                    69        0.00          0.03
        direct path write                               2        0.00          0.00
        log file sync                                   1        0.00          0.00
        SQL*Net message to client                       1        0.00          0.00
        SQL*Net message from client                     1        0.04          0.04
      
      Test 2
      ---------
      alter index test1_idx rebuild online
      
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        1      0.00       0.00          0          3          1           0
      Execute      1     66.01     124.30     205809     205288      26679           0
      Fetch        0      0.00       0.00          0          0          0           0
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total        2     66.01     124.31     205809     205291      26680           0
      
      Misses in library cache during parse: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 308
      
      Rows     Row Source Operation
      -------  ---------------------------------------------------
            1  INDEX BUILD NON UNIQUE TEST1_IDX (cr=208223 pr=205805 pw=24993 time=0 us)(object id 0)
      21408536   SORT CREATE INDEX (cr=205087 pr=205804 pw=0 time=10605921 us)
      21408536    TABLE ACCESS FULL TEST1 (cr=205087 pr=205804 pw=0 time=77541120 us cost=45401 size=85634208 card=14272368)
      
      
      Elapsed times include waiting on following events:
        Event waited on                             Times   Max. Wait  Total Waited
        ----------------------------------------   Waited  ----------  ------------
        db file sequential read                       804        0.00          0.22
        db file scattered read                      12813        0.05         66.81
        latch: object queue header operation            1        0.00          0.00
        control file sequential read                   95        0.00          0.02
        control file parallel write                    69        0.00          0.03
        direct path write                               2        0.00          0.00
        reliable message                                3        0.00          0.00
        enq: RO - fast object reuse                     1        0.00          0.00
        enq: CR - block range reuse ckpt                1        0.00          0.00
        log file sync                                   1        0.00          0.00
        SQL*Net message to client                       1        0.00          0.00
        SQL*Net message from client                     1        0.04          0.04
      That threshold is culprit ? If so then why on second case it didn't affect.
        • 1. Re: Index Build 11g
          P.Forstmann
          It looks like that to read index data ALTER INDEX REBUILD is only using direct path read I/O (bypassing buffer cache) while ALTER INDEX REBUILD ONLINE is only using buffer cache (you can find some explanation for online rebuild in http://jonathanlewis.wordpress.com/2009/06/05/online-rebuild/).

          To confirm this you can try to take snapshot of V$SESSTAT for following statistics:
          - consistent gets direct
          - db blocks gets direct
          - consistent gets from cache
          - db blocks gets from cache
          (from http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/stats002.htm#i375475).

          Edited by: P. Forstmann on 2 oct. 2010 09:45

          Edited by: P. Forstmann on 2 oct. 2010 09:47
          • 2. Re: Index Build 11g
            Jonathan Lewis
            Taral wrote:

            That threshold is culprit ? If so then why on second case it didn't affect.
            Index rebuild online always seems to need a tablescan - for no obvious reason that I can come up with. It's possible that the difference you've come up with here is simply a further manifestation of that anomaly.

            Perhaps the person (or team) tasked with writing the code for online rebuild simply made a call to tablescan the table - bypassing any of the decision-making code that could allow Oracle to choose an index scan, or adaptive direct path reads.

            If you repeat the 'flush'/'rebuild' several times in a row, does it always do the same thing ?
            What if the table size is (say) twice the size of your buffer cache ?


            Regards
            Jonathan Lewis
            • 3. Re: Index Build 11g
              Taral
              Thank you Forstmann and Jonathan for reply.

              Jonathan,

              Here are the further test i had done.
              drop table test1 purge;
              create table test1 nologging as select * from dba_objects;
              insert /*+ append */ into test1 select * from test1;
              commit;
              insert /*+ append */ into test1 select * from test1;
              commit;
              insert /*+ append */ into test1 select * from test1;
              commit;
              insert /*+ append */ into test1 select * from test1;
              commit;
              exec dbms_stats.gather_table_stats(user,'TEST1');
              create index test1_idx on test1(object_id);
              
              alter index test1_idx unusable;
              alter system flush buffer_cache;
              insert /*+ append */ into test1 select * from test2;
              commit;
              @trace
              alter /*+ 1st */ index test1_idx rebuild online;
              
              alter index test1_idx unusable;
              alter system flush buffer_cache;
              insert /*+ append */ into test1 select * from test2;
              commit;
              alter /*+ 2nd */ index test1_idx rebuild online;
              
              alter index test1_idx unusable;
              alter system flush buffer_cache;
              insert /*+ append */ into test1 select * from test2;
              commit;
              alter /*+ 3rd */ index test1_idx rebuild online;
              
              alter index test1_idx unusable;
              alter system flush buffer_cache;
              insert /*+ append */ into test1 select * from test2;
              commit;
              alter /*+ 4th */ index test1_idx rebuild online;
              
              ===========
              
              
              alter /*+ 1st */ index test1_idx rebuild online
              
              
              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        1      0.00       0.00          0          3          1           0
              Execute      1     64.83     107.37     205809     205288      26679           0
              Fetch        0      0.00       0.00          0          0          0           0
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total        2     64.83     107.37     205809     205291      26680           0
              
              Misses in library cache during parse: 1
              Optimizer mode: ALL_ROWS
              Parsing user id: 308
              
              Rows     Row Source Operation
              -------  ---------------------------------------------------
                    1  INDEX BUILD NON UNIQUE TEST1_IDX (cr=208223 pr=205805 pw=24993 time=0 us)(object id 0)
              21408536   SORT CREATE INDEX (cr=205087 pr=205804 pw=0 time=10408031 us)
              21408536    TABLE ACCESS FULL TEST1 (cr=205087 pr=205804 pw=0 time=61564416 us cost=45401 size=85634208 card=14272368)
              
              
              Elapsed times include waiting on following events:
                Event waited on                             Times   Max. Wait  Total Waited
                ----------------------------------------   Waited  ----------  ------------
                db file sequential read                       804        0.01          0.27
                db file scattered read                      12813        0.05         51.02
                control file sequential read                   95        0.00          0.03
                control file parallel write                    69        0.00          0.05
                direct path write                               2        0.00          0.00
                reliable message                                2        0.00          0.00
                enq: RO - fast object reuse                     1        0.00          0.00
                Disk file operations I/O                        1        0.00          0.00
                enq: CR - block range reuse ckpt                1        0.00          0.00
                log file sync                                   1        0.00          0.00
                SQL*Net message to client                       1        0.00          0.00
                SQL*Net message from client                     1        0.04          0.04
              ========
              alter /*+ 2nd */ index test1_idx rebuild online
              
              
              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        1      0.00       0.00          0          3          1           0
              Execute      1     87.50     150.16     274406     273715      35573           0
              Fetch        0      0.00       0.00          0          0          0           0
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total        2     87.50     150.16     274406     273718      35574           0
              
              Misses in library cache during parse: 1
              Optimizer mode: ALL_ROWS
              Parsing user id: 308
              
              Rows     Row Source Operation
              -------  ---------------------------------------------------
                    1  INDEX BUILD NON UNIQUE TEST1_IDX (cr=277640 pr=274405 pw=33328 time=0 us)(object id 0)
              28544704   SORT CREATE INDEX (cr=273448 pr=274405 pw=0 time=14028356 us)
              28544704    TABLE ACCESS FULL TEST1 (cr=273448 pr=274405 pw=0 time=87915616 us cost=45401 size=85634208 card=14272368)
              
              
              Elapsed times include waiting on following events:
                Event waited on                             Times   Max. Wait  Total Waited
                ----------------------------------------   Waited  ----------  ------------
                db file scattered read                      17084        0.05         74.01
                db file sequential read                      1066        0.01          0.30
                control file sequential read                  126        0.00          0.03
                control file parallel write                    92        0.00          0.05
                direct path write                               2        0.00          0.00
                reliable message                                2        0.00          0.00
                enq: RO - fast object reuse                     1        0.00          0.00
                enq: CR - block range reuse ckpt                1        0.00          0.00
                log file sync                                   1        0.00          0.00
                SQL*Net message to client                       1        0.00          0.00
                SQL*Net message from client                     1        0.45          0.45
              =======
              alter /*+ 3rd */ index test1_idx rebuild online
              
              
              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        1      0.01       0.00          0          3          1           0
              Execute      1    119.39     193.95     395480     342146      44447           0
              Fetch        0      0.00       0.00          0          0          0           0
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total        2    119.40     193.96     395480     342149      44448           0
              
              Misses in library cache during parse: 1
              Optimizer mode: ALL_ROWS
              Parsing user id: 308
              
              Rows     Row Source Operation
              -------  ---------------------------------------------------
                    1  INDEX BUILD NON UNIQUE TEST1_IDX (cr=347031 pr=395478 pw=94123 time=0 us)(object id 0)
              35680872   SORT CREATE INDEX (cr=341815 pr=395477 pw=52471 time=19389954 us)
              35680872    TABLE ACCESS FULL TEST1 (cr=341815 pr=343006 pw=0 time=107835760 us cost=45401 size=85634208 card=14272368)
              
              
              Elapsed times include waiting on following events:
                Event waited on                             Times   Max. Wait  Total Waited
                ----------------------------------------   Waited  ----------  ------------
                db file scattered read                      21355        0.06         90.42
                db file sequential read                      1335        0.02          0.41
                direct path write temp                          1        0.00          0.00
                direct path read temp                        1859        0.06          0.63
                control file sequential read                  158        0.00          0.04
                control file parallel write                   113        0.00          0.06
                asynch descriptor resize                        1        0.00          0.00
                direct path write                               2        0.00          0.00
                reliable message                                2        0.00          0.00
                enq: RO - fast object reuse                     1        0.00          0.00
                enq: CR - block range reuse ckpt                1        0.00          0.00
                log file sync                                   1        0.00          0.00
                SQL*Net message to client                       1        0.00          0.00
                SQL*Net message from client                     1        0.04          0.04
              ======
              alter /*+ 4th */ index test1_idx rebuild online
              
              
              call     count       cpu    elapsed       disk      query    current        rows
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              Parse        1      0.00       0.00          0          3          1           0
              Execute      1    143.09     236.85     474577     410574      53344           0
              Fetch        0      0.00       0.00          0          0          0           0
              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
              total        2    143.09     236.85     474577     410577      53345           0
              
              Misses in library cache during parse: 1
              Optimizer mode: ALL_ROWS
              Parsing user id: 308
              
              Rows     Row Source Operation
              -------  ---------------------------------------------------
                    1  INDEX BUILD NON UNIQUE TEST1_IDX (cr=416449 pr=474574 pw=112952 time=0 us)(object id 0)
              42817040   SORT CREATE INDEX (cr=410177 pr=474573 pw=62966 time=24973548 us)
              42817040    TABLE ACCESS FULL TEST1 (cr=410177 pr=411607 pw=0 time=133080616 us cost=45401 size=85634208 card=14272368)
              
              
              Elapsed times include waiting on following events:
                Event waited on                             Times   Max. Wait  Total Waited
                ----------------------------------------   Waited  ----------  ------------
                db file sequential read                      1603        0.01          0.45
                db file scattered read                      25626        0.05        112.27
                direct path write temp                         22        0.02          0.05
                asynch descriptor resize                        3        0.00          0.00
                direct path read temp                        2895        0.09          1.30
                control file sequential read                  185        0.00          0.05
                control file parallel write                   137        0.00          0.07
                direct path write                               5        0.02          0.04
                reliable message                                2        0.00          0.00
                enq: RO - fast object reuse                     1        0.00          0.00
                enq: CR - block range reuse ckpt                1        0.00          0.00
                log file sync                                   1        0.00          0.00
                SQL*Net message to client                       1        0.00          0.00
                SQL*Net message from client                     1        0.04          0.04
              Does, it happen to do anything with locking of table. When you rebuild it lock table in shared mode. While online rebuild lock it in row shared mode.