1 2 Previous Next 18 Replies Latest reply: Jan 24, 2013 3:23 AM by 986872 RSS

    Alter Index Rebuild Online

    gpafogarty
      My database is release 10gR2. I sometimes execute command ALTER INDEX <index name> REBUILD ONLINE on indexes that are 500 MB in size. It takes about ten minutes to run the command. The database alert log shows no activity for the first six minutes, and DML is allowed on the associated table. At about minute seven the alert log shows the first in a series of five switches of the online redo logs. They are each 100 MB.

      During the time that the online redo logs are switching, sessions executing DML are blocked from completing. When the new version of the index has been created the online redo logs stop switching, the original copy of the index disappears, the ALTER INDEX command ends, and the blocking of DML ceases.

      I have read that DML statements are not affected by the execution of an ALTER INDEX REBUILD ONLINE, but experience shows the blocking of sessions described above. Can someone explain to me what takes place during the various stages of the index rebuild? Is there something I can do to eliminate the blocking of DML statements during the period when online redo logs switch?

      Thank you,
      Bill
        • 1. Re: Alter Index Rebuild Online
          sb92075
          ALTER INDEX <index name> REBUILD ONLINE
          WHY?
          • 2. Re: Alter Index Rebuild Online
            Charles Hooper
            Bill,

            A helpful reference for you:
            http://jonathanlewis.wordpress.com/2009/06/05/online-rebuild/

            Another extremely helpful reference - many articles:
            http://richardfoote.wordpress.com/category/index-rebuild/

            For what reason are you rebuilding the indexes?

            Charles Hooper
            Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
            http://hoopercharles.wordpress.com/
            IT Manager/Oracle DBA
            K&M Machine-Fabricating, Inc.
            • 3. Re: Alter Index Rebuild Online
              user12081442
              Consider using PARALLEL and NOLOGGING, as in this approach:



              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1412203938893



              From the Oracle9i Data Warehousing Guide manual:

              [NO]LOGGING Clause

              The [NO]LOGGING clause applies to tables, partitions, tablespaces, and indexes. Virtually no log is generated for certain operations (such as direct-path INSERT) if the NOLOGGING clause is used. The NOLOGGING attribute is not specified at the INSERT statement level but is instead specified when using the ALTER or CREATE statement for a table, partition, index, or tablespace.

              When a table or index has NOLOGGING set, neither parallel nor serial direct-path INSERT operations generate undo or redo logs. Processes running with the NOLOGGING option set run faster because no redo is generated. However, after a NOLOGGING operation against a table, partition, or index, if a media failure occurs before a backup is taken, then all tables, partitions, and indexes that have been modified might be corrupted.
              Note:

              Direct-path INSERT operations (except for dictionary updates) never generate undo logs. The NOLOGGING attribute does not affect undo, only redo. To be precise, NOLOGGING allows the direct-path INSERT operation to generate a negligible amount of redo (range-invalidation redo, as opposed to full image redo).

              For backward compatibility, [UN]RECOVERABLE is still supported as an alternate keyword with the CREATE TABLE statement. This alternate keyword might not be supported, however, in future releases.

              At the tablespace level, the logging clause specifies the default logging attribute for all tables, indexes, and partitions created in the tablespace. When an existing tablespace logging attribute is changed by the ALTER TABLESPACE statement, then all tables, indexes, and partitions created after the ALTER statement will have the new logging attribute; existing ones will not change their logging attributes. The tablespace-level logging attribute can be overridden by the specifications at the table, index, or partition level.

              The default logging attribute is LOGGING. However, if you have put the database in NOARCHIVELOG mode, by issuing ALTER DATABASE NOARCHIVELOG, then all operations that can be done without logging will not generate logs, regardless of the specified logging attribute.
              Creating Indexes in Parallel

              Multiple processes can work together simultaneously to create an index. By dividing the work necessary to create an index among multiple server processes, Oracle can create the index more quickly than if a single server process created the index sequentially.

              Parallel index creation works in much the same way as a table scan with an ORDER BY clause. The table is randomly sampled and a set of index keys is found that equally divides the index into the same number of pieces as the DOP. A first set of query processes scans the table, extracts key-rowid pairs, and sends each pair to a process in a second set of query processes based on key. Each process in the second set sorts the keys and builds an index in the usual fashion. After all index pieces are built, the parallel coordinator simply concatenates the pieces (which are ordered) to form the final index.

              Parallel local index creation uses a single server set. Each server process in the set is assigned a table partition to scan and for which to build an index partition. Because half as many server processes are used for a given DOP, parallel local index creation can be run with a higher DOP.

              You can optionally specify that no redo and undo logging should occur during index creation. This can significantly improve performance but temporarily renders the index unrecoverable. Recoverability is restored after the new index is backed up. If your application can tolerate a window where recovery of the index requires it to be re-created, then you should consider using the NOLOGGING clause.

              The PARALLEL clause in the CREATE INDEX statement is the only way in which you can specify the DOP for creating the index. If the DOP is not specified in the parallel clause of CREATE INDEX, then the number of CPUs is used as the DOP. If there is no PARALLEL clause, index creation is done serially.
              Note:

              When creating an index in parallel, the STORAGE clause refers to the storage of each of the subindexes created by the query server processes. Therefore, an index created with an INITIAL of 5 MB and a DOP of 12 consumes at least 60 MB of storage during index creation because each process starts with an extent of 5 MB. When the query coordinator process combines the sorted subindexes, some of the extents might be trimmed, and the resulting index might be smaller than the requested 60 MB.

              When you add or enable a UNIQUE or PRIMARY KEY constraint on a table, you cannot automatically create the required index in parallel. Instead, manually create an index on the desired columns, using the CREATE INDEX statement and an appropriate PARALLEL clause, and then add or enable the constraint. Oracle then uses the existing index when enabling or adding the constraint.

              Multiple constraints on the same table can be enabled concurrently and in parallel if all the constraints are already in the ENABLE NOVALIDATE state. In the following example, the ALTER TABLE ... ENABLE CONSTRAINT statement performs the table scan that checks the constraint in parallel:

              CREATE TABLE a (a1 NUMBER CONSTRAINT ach CHECK (a1 > 0) ENABLE NOVALIDATE)
              PARALLEL;
              INSERT INTO a values (1);
              COMMIT;
              ALTER TABLE a ENABLE CONSTRAINT ach;
              • 4. Re: Alter Index Rebuild Online
                gpafogarty
                The indexes grow in size by fifty percent or more in a few months. There is a limited amount of space in the index tablespaces, and rebuilding these large indexes significantly increases freespace there. ASM is not turned on in these tablespaces, so I cannot do a shrink. Migrating the indexes to a tablespace with ASM turned on is possible, but it would be a significant undertaking. It seems simpler to rebuild the indexes periodically.

                Thanks,
                Bill
                • 5. Re: Alter Index Rebuild Online
                  gpafogarty
                  Charles,

                  The link to the blog by Richard Foote was helpful. The entry titled "Index Create and Rebuild Locking Improvements in 11g (Ch Ch Ch Changes)" contains a description of how the online index rebuild can create locks that block other sessions. Anyone interested in an answer to my original question may learn something by following that link.

                  Thank you,
                  Bill
                  • 6. Re: Alter Index Rebuild Online
                    Charles Hooper
                    Bill,

                    That is great to hear. Richard is the Oracle indexing expert. I just could not remember the exact article where he described what happened during an online rebuild, but I was certain that it could be located quickly from the link that I posted.

                    Just one clarification for anyone else reading this thread. ASM and ASSM are different technologies. I do not believe that indexes need to be in an ASSM tablespace to be coalesced.

                    Charles Hooper
                    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                    http://hoopercharles.wordpress.com/
                    IT Manager/Oracle DBA
                    K&M Machine-Fabricating, Inc.
                    • 7. Re: Alter Index Rebuild Online
                      Jonathan Lewis
                      gpafogarty wrote:
                      My database is release 10gR2. I sometimes execute command ALTER INDEX <index name> REBUILD ONLINE on indexes that are 500 MB in size. It takes about ten minutes to run the command. The database alert log shows no activity for the first six minutes, and DML is allowed on the associated table. At about minute seven the alert log shows the first in a series of five switches of the online redo logs. They are each 100 MB.
                      If we assume that most of the work in that last three minutes is reading from the temp segment, writing the index segment, and writing the redo log, then 3 minutes for 500MB seems rather slow. It's possible that your I/O subsystem is getting overloaded at this point and is slowing things down. It's also possible that a significant part of the three minutes is the "catchup" from the materialized view log that Oracle creates to record changes to the table for the 10 minutes - if your system is very active on this table it's possible that your problem relates to contention caused by this activity.
                      During the time that the online redo logs are switching, sessions executing DML are blocked from completing. When the new version of the index has been created the online redo logs stop switching, the original copy of the index disappears, the ALTER INDEX command ends, and the blocking of DML ceases.

                      I have read that DML statements are not affected by the execution of an ALTER INDEX REBUILD ONLINE, but experience shows the blocking of sessions described above. Can someone explain to me what takes place during the various stages of the index rebuild? Is there something I can do to eliminate the blocking of DML statements during the period when online redo logs switch?
                      In principle (for 10g):
                      Your rebuild tries to take an exclusive (or possibly share) lock on the table and gets stuck waiting for current transactions on the table to complete.
                      All current transactions on the table complete, all new ones are stuck behind the rebuild lock
                      The rebuild gets its lock, prepares the MV log, starts the rebuild process, and releases the lock
                      All waiting transactions continue
                      Your rebuild does a massive tablescan and sort to prepare the index and copies it into place
                      Your rebuild tries to take an exclusive (or possibly share) lock on the table and gets stuck waiting for current transactions on the table to complete.
                      All current transactions on the table complete, all new ones are stuck behind the rebuild lock
                      The rebuild gets its lock applies the catch-up data to the new index, drops the mv log and old index and releases the lock
                      All waiting transaction continue



                      I don't know the exact timing of how Oracle locks the table the second time, and when it applies the catch-up, and how it tries to minimise the time for holding the lock. But I think you are being delayed by application of the log in a way that is not supposed to happen. This is why I made the comment about the volume of activity on the table.

                      In passing, as Richard points out in his blog, the locking mechanisms have changed in 11g - perhaps to work around the problem you are seeing. You might like to trawl Metalink to see if you can find any notes relating to the change and to the final clearing up of the change log on the online rebuild.

                      Regards
                      Jonathan Lewis
                      http://jonathanlewis.wordpress.com
                      http://www.jlcomp.demon.co.uk

                      To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                      {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                      fixed format
                      .
                      
                      There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
                      
                      +"Science is more than a body of knowledge; it is a way of thinking"+
                      +Carl Sagan+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                      • 8. Re: Alter Index Rebuild Online
                        718419
                        >
                        Your rebuild tries to take an exclusive (or possibly share) lock on the table and gets stuck waiting for current transactions on the table to complete.
                        All current transactions on the table complete, all new ones are stuck behind the rebuild lock
                        The rebuild gets its lock, prepares the MV log, starts the rebuild process, and releases the lock
                        All waiting transactions continue
                        Your rebuild does a massive tablescan and sort to prepare the index and copies it into place
                        Your rebuild tries to take an exclusive (or possibly share) lock on the table and gets stuck waiting for current transactions on the table to complete.
                        All current transactions on the table complete, all new ones are stuck behind the rebuild lock
                        The rebuild gets its lock applies the catch-up data to the new index, drops the mv log and old index and releases the lock
                        All waiting transaction continue


                        the last two step has a little flaw.
                        the rebuild gets its lock and release lock immediately
                        the rebuild applies the catch-up data to the new index, drops the mv log and old index and releases the lock
                        all waiting transaction continue

                        the last two step can go ahead in parallel.
                        rebuild get the lock just to mark that it only need to apply the updates between it start get the lock first and release the lock second time.
                        • 9. Re: Alter Index Rebuild Online
                          Jonathan Lewis
                          jametong wrote:
                          >
                          The rebuild gets its lock applies the catch-up data to the new index, drops the mv log and old index and releases the lock
                          All waiting transaction continue
                          the last two step has a little flaw.
                          the rebuild gets its lock and release lock immediately
                          the rebuild applies the catch-up data to the new index, drops the mv log and old index and releases the lock
                          all waiting transaction continue

                          the last two step can go ahead in parallel.
                          rebuild get the lock just to mark that it only need to apply the updates between it start get the lock first and release the lock second time.
                          JameTong,

                          How certain are you that this is the case ? It's definitely a better strategy from the perspective of concurrency, but I think it introduces a new complication to the code which now has to handle the following scenario:

                          <blockquote>
                          The rows X and Y are in the MV log but not in the rebuilt index
                          The rebuild takes it's lock, releases it, and starts the catchup
                          My session queries the data by index and ought to see rows X and Y - so it has to use the old index because these entries are not yet in the new index
                          My session inserts row W - if I've understood your comment the entry won't go through the MV log, so has to be applied to the new index
                          My session queries the data by index to see rows W, X and Y -- how does it get the right result ?
                          </blockquote>

                          I can think of a couple of strategies which would allow the "instant release" to work - the easiest one is that the rebuild process simply keeps repeating the whole cycle after each batch of updates it does using the SCN from the moment the previous lock was acquired to identify which rows it should apply on the next pass, and stops when there are no rows left to apply. I don['t have time at the moment to build a model to test this. Do you have any information on what happens in this case ?



                          Regards
                          Jonathan Lewis
                          • 10. Re: Alter Index Rebuild Online
                            Aravind N
                            Hi,

                            Might be off topic, but I would like to get more clarity on the below statement from Jonathan
                            Jonathan Lewis wrote:
                            It's also possible that a significant part of the three minutes is the "catchup" from the materialized view log that Oracle creates to record changes to the table for the 10 minutes
                            I am aware about a journal table getting created while doing the online index rebuild. Can you explain a bit about the MV log?

                            Thanks
                            Aravind
                            • 11. Re: Alter Index Rebuild Online
                              718419
                              Jonathan Lewis wrote:
                              jametong wrote:
                              >
                              The rebuild gets its lock applies the catch-up data to the new index, drops the mv log and old index and releases the lock
                              All waiting transaction continue
                              the last two step has a little flaw.
                              the rebuild gets its lock and release lock immediately
                              the rebuild applies the catch-up data to the new index, drops the mv log and old index and releases the lock
                              all waiting transaction continue

                              the last two step can go ahead in parallel.
                              rebuild get the lock just to mark that it only need to apply the updates between it start get the lock first and release the lock second time.
                              JameTong,

                              How certain are you that this is the case ? It's definitely a better strategy from the perspective of concurrency, but I think it introduces a new complication to the code which now has to handle the following scenario:

                              <blockquote>
                              The rows X and Y are in the MV log but not in the rebuilt index
                              The rebuild takes it's lock, releases it, and starts the catchup
                              My session queries the data by index and ought to see rows X and Y - so it has to use the old index because these entries are not yet in the new index
                              My session inserts row W - if I've understood your comment the entry won't go through the MV log, so has to be applied to the new index
                              My session queries the data by index to see rows W, X and Y -- how does it get the right result ?
                              </blockquote>

                              I can think of a couple of strategies which would allow the "instant release" to work - the easiest one is that the rebuild process simply keeps repeating the whole cycle after each batch of updates it does using the SCN from the moment the previous lock was acquired to identify which rows it should apply on the next pass, and stops when there are no rows left to apply. I don['t have time at the moment to build a model to test this. Do you have any information on what happens in this case ?



                              Regards
                              Jonathan Lewis

                              I have done a detailed test on this issue with help of 10046 and 10704 trace event.

                              After the new indexes been created with FULL TABLE SCAN, the rebuild process will first apply all the committed Journal log in the IOT Table, and then acquire the Share Lock on the Base Table. When all the blocking session committed, the rebuild process apply the journal log generated by the blocking session, and then process the dictionary tables(drop the journal table and change the data_object_id of the index being rebuilt), Then the Share Lock on the Base Table will be released.

                              Details can be found at index rebuild online full description

                              Test Env:

                              Os Windows XP
                              Oracle : Oracle 9201 32bit Edition

                              Edited by: jametong on Aug 8, 2010 8:41 PM
                              • 12. Re: Alter Index Rebuild Online
                                Jonathan Lewis
                                Aravind N wrote:
                                Jonathan Lewis wrote:
                                It's also possible that a significant part of the three minutes is the "catchup" from the materialized view log that Oracle creates to record changes to the table for the 10 minutes
                                I am aware about a journal table getting created while doing the online index rebuild. Can you explain a bit about the MV log?
                                Aravind,

                                My error - I used the expression "materialized view log" when I should have said "journal table". In principle the concepts are very similar, in practice the implementations look very different.

                                Regards
                                Jonathan Lewis
                                • 13. Re: Alter Index Rebuild Online
                                  Jonathan Lewis
                                  jametong wrote:
                                  Jonathan Lewis wrote:

                                  I can think of a couple of strategies which would allow the "instant release" to work - the easiest one is that the rebuild process simply keeps repeating the whole cycle after each batch of updates it does using the SCN from the moment the previous lock was acquired to identify which rows it should apply on the next pass, and stops when there are no rows left to apply. I don['t have time at the moment to build a model to test this. Do you have any information on what happens in this case ?

                                  I have done a detailed test on this issue with help of 10046 and 10704 trace event.

                                  After the new indexes been created with FULL TABLE SCAN, the rebuild process will first apply all the committed Journal log in the IOT Table, and then acquire the Share Lock on the Base Table. When all the blocking session committed, the rebuild process apply the journal log generated by the blocking session, and then process the dictionary tables(drop the journal table and change the data_object_id of the index being rebuilt), Then the Share Lock on the Base Table will be released.

                                  Details can be found at index rebuild online full description

                                  Test Env:
                                  Os Windows XP
                                  Oracle : Oracle 9201 32bit Edition
                                  JameTong,

                                  Excellent, thanks for that.

                                  If I've read your demonstration, and description above, correctly then the "journal apply" takes place in two phases;
                                  <blockquote>
                                  Step 1: Apply all committed changes (i.e. the ones that can currently be seen in the journal)
                                  Step 2: Lock (waiting as necessary) the base table to stop new entries going into the journal, apply any changes not applied in step 1, clean up data dictionary.
                                  </blockquote>

                                  It would be interesting to know what dirty little tricks Oracle has to play internally to make this work efficiently and without generating errors.

                                  Regards
                                  Jonathan Lewis
                                  • 14. Re: Alter Index Rebuild Online
                                    Aravind N
                                    Thanks Jonathan.
                                    1 2 Previous Next