Oracle Scratchpad

May 30, 2010

Index Rebuilds

Filed under: Index Rebuilds,Indexing,Oracle — Jonathan Lewis @ 7:57 pm GMT 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.

10 Comments »

  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 GMT 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 GMT 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 GMT 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 GMT 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.

    Regards

    Nigel.

    Comment by Nigel Noble — June 2, 2010 @ 11:30 am GMT 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): http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/9961

    Regards
    Stefan

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

    Comment by Stefan — June 7, 2010 @ 1:04 pm GMT 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 10.2.0.1 (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 GMT Jun 8,2010 | Reply

      • Hello Jonathan,
        afaik the feature with event ‘10629’ was introduced in version 9.2.0.6 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.

        Regards
        Stefan

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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,521 other followers