This discussion is archived
7 Replies Latest reply: Nov 23, 2012 1:23 AM by 767685 RSS

Index REBUILD PARTITION not completing

767685 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Expert
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points