Oracle Scratchpad

May 30, 2010

Index Rebuilds

Filed under: Index Rebuilds,Indexing,Oracle — Jonathan Lewis @ 7:57 pm BST May 30,2010

A question came up on OTN a little while ago about an oddity during an online index rebuild. The original question describes how a relatively small index (500MB) takes several minutes to rebuild, with a few minutes where users who are trying to update the table seem to be stuck on enqueue wait events, waiting for locks on the table to be released. But Oracle tells us that “online” rebuilds of indexes don’t cause long-lasting locks to appear – is this a bug, or a new myth ?

My response to the question is speculative but, I hope, a reasonable suggestion that fits the original description. The amount of time that the locks are held at either end of the rebuild are dependent on the level (and timing) of activity on the table.

In Oracle 10g, I have managed to cause long waits for other users – even when the process doing the index rebuild took fractions of a second to get its final lock – by generating a very large volume of change as the initial stage of the index rebuild was taking place.

Note: this was not the same problem as the process doing the rebuild having to wait for the final lock because another user had uncommitted transactions on the table.


  1. hi Jonathan:
    I think the steps you post at the forum has a little flaw. When rebuild gets the index done,it will try to get a exclusive lock, but as soon as it get the lock, it will release the lock immediately. It gets the lock only to make a mark,to tell the system it know where to start the MV logging and where to stop the MV logging, and how many updated it need to apply to the rebuilt new index.
    I have posted similar info the forum.

    Comment by jametong — May 31, 2010 @ 5:49 am BST May 31,2010 | Reply

    • jametong,

      Thanks for the comment – I’ve added a further note to the OTN thread (rather than split the discussion between two places)raising the question of how Oracle handles a scenario in the interval between the lock being released and the last update being applied. If you know how Oracle handles that case I’d be interested in hearing the details; it strikes me that scenarios of that type might be the reason why the code changed between 10g and 11g.

      Comment by Jonathan Lewis — May 31, 2010 @ 7:13 am BST May 31,2010 | Reply

      • I have done a more detailed test on this issue.

        In Oracle 9201, when index recreate process finished,
        Oracle will first apply all the changes committed to the journal log,
        Then acquire an Share Lock on the Base Table,
        Until it get the Share lock,
        Then Oracle will apply the journal log in the IOT Table after previous apply(the changes made by the sessions blocking the acquire of the Share Lock),
        Then Oracle will clear the interim state(drop the journal table),
        Then Oracle will change the dictionary info about the index(mainly change the data_object_id of the index),
        Then Oracle release the Share Lock and the Rebuild index process finished completely.

        Comment by jametong — August 9, 2010 @ 3:50 am BST Aug 9,2010 | Reply

  2. […] — Nigel Noble @ 12:24 pm I’ve just seen a note on Jonathan Lewis’s blog regarding Online Index Rebuilds. It reminds me of some issues which existed in Oracle 9i and 10g but appear to have been resolved […]

    Pingback by Online Index Rebuilds « Nigel Noble's Oracle Blog — June 2, 2010 @ 11:24 am BST Jun 2,2010 | Reply

  3. Hi Jonathan,

    I have just put a short note on my blog regarding some locking related to online index rebuilds. It’s a bit more detail on the issue with long active transactions blocking online rebuilds.



    Comment by Nigel Noble — June 2, 2010 @ 11:30 am BST Jun 2,2010 | Reply

  4. Hello Jonathan,
    the event ‘10629’ prevents holding such long locks by REBUILD INDEX ONLINE (locking the TM enqueue permanently).

    The different levels of event ‘10629’ are:
    – Level 1 tries as often as required to obtain the enqueue
    – Level 32, 64, 96, .. attempts times to obtain the enqueue. If it does not succeed after the specified number of attempts, ORA-00054 occurs.

    I also wrote an blog about it on my SAP page (not sure if you are able to see it without having an account on SDN):


    P.S.: Looking forward to your event with Kyle Hailey on 10/06/2010.

    Comment by Stefan — June 7, 2010 @ 1:04 pm BST Jun 7,2010 | Reply

    • Stefan,

      Thanks for the information.
      Do you have any idea if it behaves the same way for both the starting and ending lock – and if there are any variations with version ?
      I have the following from the oraus.msg file from (which is less informative than your information):

      10629, 00000, "force online index build to backoff and retry DML lock upgrade"
      // *Cause:
      // *Action: set this event only under the supervision of Oracle development
      // *Comment: Change the behaviour of an online index rebuild such that it 
      //           will backoff and retry a failed DML lock upgrade.
      //           The event level is the number of retries the online index rebuild
      //           should wait. Level 1 means backoff and retry indefinitely. Any 
      //           other value less than 32 will be adjusted automatically to be 32.

      Comment by Jonathan Lewis — June 8, 2010 @ 9:53 pm BST Jun 8,2010 | Reply

      • Hello Jonathan,
        afaik the feature with event ‘10629’ was introduced in version and did not changed until now (but not quite sure about 11gR2 .. not used it intensely until now)

        Directly after the start and before the end of the create/rebuild, the table is completely locked. If there are no open transactions on the table in question, these locks are only retained for a short time. However, if there are open transactions on the table in question when the lock is requested (TX enqueue) for the create/rebuild, the rebuild waits until all of these transactions are committed. Event ‘10629’ was introcduced to avoid such situations at the start and end.

        By the way, if you are using leve = 1 the cleanup by SMON differs and an ORA-08104 can occur.


        Comment by Stefan — June 9, 2010 @ 12:45 pm BST Jun 9,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: