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 in 11gR1 and 11gR2. Oracle 9i introduced a patch to change behaviour regarding online Index Rebuilds. The default behaviour in 9i and 10g is that an Online Index Rebuild would get blocked behind a long active transaction which uses the index (which is still true in 11g) but critically then would also block any new DML wanting to also modify the index (Leading to a hang of the application as well as the index build). They introduced a new database EVENT 10629 (in a 9i patch) which would mean the Online Index Rebuild would keep trying to acquire its locks but would keep backing off to allow other DML to continue. The Event would be set with a level to either try forever (but don’t block other new DML) or fail the Online Index Rebuild after a specific time period (well retries).
Out of interest, you can sometimes look up the text for Oracle Events using the “oerr” command .
$ oerr ora 10629 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.
There is more information on Meta link (note: 3566511.8). The comments of “This issue is fixed in” mean the new Event feature is included in the release (you still need to set it).
I’ve just had a very quick test of the Event on 11gR1/R2 but it’s not clear if the Event still works. The very important thing (to me) is the 11g versions no longer cause other unrelated DML to become stuck behind a long running active transaction.