7 Replies Latest reply: Nov 23, 2012 3:23 AM by 767685 RSS

    Index REBUILD PARTITION not completing

    767685
      Hello,

      Index with following PARTITIONS. Index rebuild is extremely slow. Below 2 Alter index ..rebuild... took 10 hours to complete. Because of this queries which based on this index are really slow. Please suggest where to look for the root cause or suggestions to sort it out.
      SYS@DB AS SYSDBA> select partition_name,tablespace_name,bytes/1024 KB from dba_segments where segment_name='KSTN';
      
      PARTITION_NAME    TABLESPACE_NAME           KB
      ------------------------------ ------------------------------ ----------
      REB_IDX_1            TS_REB                     64
      REB_IDX_2            TS_REB                     64
      REB_IDX_3            INDEX                        64
      REB_IDX_4            TS_REB                     64
      REB_IDX_5            TS_REB                     64
      REB_IDX_6            TS_REB                     64
      REB_IDX_7            TS_REB                     64
      REB_IDX_X              TS_REB                2228224
      
      
      alter index KSTNG rebuild PARTITION REB_IDX_1 tablespace TS_REB online;
      alter index KSTNG rebuild PARTITION REB_IDX_2 tablespace TS_REB online;
      Thanks a lot.

      Best Regards
        • 1. Re: Index REBUILD PARTITION not completing
          Girish Sharma
          If you're using the ONLINE rebuild option it might take a while if there are active transactions on the table, since it waits until no transactions are active to start the rebuild process, the same applies to the completion of the ONLINE operation.

          Randolf Geist @ Index rebuilding slow in Oracle 10g

          So, without trace on that session (who have issued alter index....) its really difficult to find out the problem. Tracing will generate the trace file which tells you where and why it is stuck for 10 Hrs.

          Regards
          Girish Sharma
          • 2. Re: Index REBUILD PARTITION not completing
            767685
            Thanks a lot for the reply.

            Meanwhile I found from the awr report and from the oem performance opton that during INDEX REBUILD, db_file_scattered_read is the top wait event.

            Any suggestion to avoide hours and hours of wait due to db_file_scattered_read.?

            Thanks.

            best regards
            • 3. Re: Index REBUILD PARTITION not completing
              6363
              John-M wrote:

              Any suggestion to avoide hours and hours of wait due to db_file_scattered_read.?
              Since it is very rare that indexes need to be rebuilt one option to avoid it would be to consider not rebuilding them or coalesce them instead, depending on why they need to be rebuilt.

              http://richardfoote.wordpress.com/2008/02/08/index-rebuild-vs-coalesce-vs-shrink-space-pigs-3-different-ones/

              http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth-ii.pdf
              • 4. Re: Index REBUILD PARTITION not completing
                rp0428
                >
                Any suggestion to avoide hours and hours of wait due to db_file_scattered_read.?
                >
                Have you taken Girish's advice and make sure no users are accessing the table during the rebuild?
                • 5. Re: Index REBUILD PARTITION not completing
                  Iordan Iotzov
                  Were those db_file_scattered_read events generated by the index rebuild? You can confirm that with the help of the t Active Session History (ASH), a separately licensed option.

                  Iordan Iotzov
                  http://iiotzov.wordpress.com/
                  • 6. Re: Index REBUILD PARTITION not completing
                    Jonathan Lewis
                    John-M wrote:
                    Thanks a lot for the reply.

                    Meanwhile I found from the awr report and from the oem performance opton that during INDEX REBUILD, db_file_scattered_read is the top wait event.

                    Any suggestion to avoide hours and hours of wait due to db_file_scattered_read.?
                    Online rebuilds of indexes will perform tablescans of the table. Since one of your index partitions is 2GB compared, apparently, to 64KB for the other 7 partitions, it would be interesting to know how big the table partitions are in GB. It would also be useful to know what the index definition is - in particular whether or not it's a function-based index. You could also look at the session statistics while the rebuild is going on in case that gives us any clues - Tanel Poder's "snapper" script is very useful for this. It's possible that the tablescan is taking a very long time because it runs into a read-consistency problem with cleanout and keeps restarting. You might also get some clues by looking at v$session_longops about what is going on, what Oracle predicts in terms of future work, and what it has seen in terms of completed work.

                    Regards
                    Jonathan Lewis
                    • 7. Re: Index REBUILD PARTITION not completing
                      767685
                      Hello,

                      Thanks a lot for your reply, and all the other guys too. Appologize for late feedback.

                      As you said:
                      it would be interesting to know how big the table partitions are in GB
                      Here I have no idea at the moment, which index partition is on which table partition
                      SYS@db AS SYSDBA> select segment_type,tablespace_name,bytes/1024/1024 "size in MB",segment_name from dba_segments where segment_name='RST';
                      
                      SEGMENT_TYPE        TABLESPACE_NAME       size in MB SEGMENT_NAME
                      ------------------ ------------------------------ ---------- ----------
                      TABLE PARTITION    TAB                     264.875 RST
                      TABLE PARTITION    TAB                  10172.5625 RST
                      TABLE PARTITION    TAB                       10406 RST
                      TABLE PARTITION    TAB                       10591 RST
                      TABLE PARTITION    TAB                     18831.5 RST
                      TABLE PARTITION    TAB                       .0625 RST
                      TABLE PARTITION    TAB                       .0625 RST
                      TABLE PARTITION    TAB                  188891.375 RST
                      8 rows selected.

                      Best Regards