Index Create and Rebuild Locking Improvements in 11g (Ch Ch Ch Changes) February 11, 2008
Posted by Richard Foote in 11g, Index Rebuild, Locking Issues, Oracle General, Oracle Indexes.trackback
Although the CREATE INDEX … ONLINE and ALTER INDEX … REBUILD ONLINE options have been available for a long while, they can still introduce locking issues in highly active databases.
Oracle requires a table lock on the index base table at the start of the CREATE or REBUILD process (to guarantee DD information) and a lock at the end of the process (to merge index changes made during the rebuild into the final index structure).
These locks have two implications. Firstly, if there’s an active transaction on the base table of the index being created or rebuilt at the time one of these locks is required, the indexing process will hang. This will of course impact the time it takes to complete the indexing process. However the second far more serious issue is that any other active transactions on the base table starting after the indexing process hangs will likewise be locked and be prevented from continuing, until the indexing process obtains and releases its locks. In highly concurrent environments with many transactions, this can cause serious disruptions to the response times of these impacted transactions. Of course, depending on the time the initial locking transactions take to commit or rollback, this backlog of locked transactions can be quite significant.
Oracle11g has made some improvements in the locking implications regarding creating or rebuilding indexes online.
During the creation or rebuilding of an index online, Oracle still requires two associated table locks on the base table at the start and end of indexing process. If there’s an active transaction on the base table at the time one of these locks is required, the indexing process will still hang as its done previously until all these prior active transactions have completed. No change so far.
However, if the indexing process has been locked out and subsequent transactions relating to the base table start afterwards, these transactions will no longer in turn be locked out by the indexing table locks and are able to complete successfully. The indexing process no longer impacts other concurrent transactions on the base table, it will be the only process potentially left hanging while waiting to acquire its associated lock resource.
This means it may not be quite so “risky” to urgently introduce that new index or rebuild that troublesome index during core business hours due to the reduced locking implications introduced in 11g.
See this demo for Index Rebuild Locking Issues in 10g and the 11g Improvements.
Does this means we can now simply rebuild all our indexes, whenever ? Ummmm, no 😉
Richard Foote's Oracle Blog 
Hi,
I have been working on an Oracle DBA/Sysadmin Dashboard. What do you think are the key ingredients of a DBA dashboard?
Your opinion is highly regarded
Regards
Nilesh
Dashboards
LikeLike
Hi Nilesh
Gee, I need to think about it before I can give you a considered answer.
However, I guess the obvious question I have is what do you have in mind in terms of offering something that isn’t already available through Enterprise Manager, Quest Spotlight and the such ?
LikeLike
Richard, one more question about CREATE INDEX … ONLINE: it seems another serious issue with the second lock phase is that there is a timeout and in environments with high activity the CREATE INDEX might actually hang until the whole CREATE INDEX is rolled back. At least that’s what I’m hearing from a colleague. This would render ONLINE pretty useless in those environment because you would not want to invest an hour for index creation only to see three hours of rollback because the lock could not be obtained. While I think about it it may make ONLINE useless most of the time because low activity environments could easily live with non ONLINE index creation while for the high activity environments it does not work without major risk of wasted time. Did we miss something?
Cheers
LikeLike
Hi Robert
I’m unaware of such an implicit timeout. Yes, the index operation can hang indefinitely on the second lock but I’m unaware of the index operation itself timing out.
See what I can dig up …
LikeLike
Richard,
I can provide some more detail with regard to the assumed lock timeout issue with CREATE INDEX … ONLINE:
1. I could not reproduce it with SQL*Plus (unless the timeout is 2 days or more :-))
2. I have talked to the colleague and learned this: he did the index creation from “PL/SQL Developer”. Once he saw SMON accessing the OID of the index, he assumed the creation had failed and was being rolled back. So he canceled it. There is no ORA error he could present.
I learn from this:
1. Common sense had it right (ONLINE would be useless with such a timeout).
2. Assume a failure only if Oracle actually reports it.
3. My preference for SQL*Plus is reinforced. 🙂
LikeLike
Hi Robert
The only problem with common sense is that it’s sometimes not quite as common as the name suggests 😉
LikeLike
ROTFLMAO – I’ll have to print that one and stick on the wall.
Btw I was fed another bit of information – this time with ORA:
create index DUPCHECK_ACCREC on ACCREC (cre_id, accountingunit, tid_index) online parallel 3
*
ERROR at line 1:
ORA-12805: parallel query server died unexpectedly
This is kind of alarming because of the “unexpectedly” but apparently this is not directly related to ONLINE. This could also well be the error present earlier because it is the same DB instance.
To sum up: of course CREATE INDEX … ONLINE can terminate in error leading to nasty rollback but as far as we can see there is no timeout involved.
LikeLike
Hi Robert
That error is a direct result of someone killing off the Oracle parallel process. It did indeed die unexpectedly from Oracle’s point of view.
LikeLike
Hi Gurus,
I have Oracle 10gr2 Hp_Ux Environment.
I have a huge partitioned table and a procedure to rebuild index parallely depending on partitions.
I get this error frequently
ORA-20001: ORA-04022: nowait requested, but had to wait to lock dictionary
object
ORA-06512: at package name , line 917
ORA-04022: nowait requested, but had to wait to lock dictionary object
ORA-06512: at line
It is urgent me to fix it. Could anybody tell me why this error occurs and how to resolve it?
I appreciate for sharing this information.
Thanks
LikeLike
I’m no guru but the issue here is that to rebuild an index in parallel requires an exclusive lock and with current transactions hitting the related table (or other locks in place), Oracle is unable to immediately obtain the necessary locks and so generates the error.
The “fix” is to perform the rebuild when the database is “quiet” or to not perform it in parallel and perform an online rebuild instead (although this may introduce locking related performance issues in your environment).
LikeLike
Hi,
In an SE version database 10.2.0.4 the rebuild online option cannot be used. How will an index rebuild therefore affect table locking.
LikeLike
Hi John
The table will be exclusively locked throughout the whole index rebuild process. So pick your indexes and pick your time wisely.
LikeLike
Thanks for the feedback. I suspected this was the case but just required confirmation.
LikeLike
Hi Richard,
Thanks for the explanation.Another difference which i noticed while testing out coalesce and shrink space compact is that coalesce doesn’t lock the table/rows.But if you do insert and don’t commit(session 1) and alter index shrink space compact (session 2), then session 2 hangs till session 1 commits.
Thanks
Antony
LikeLike
Hi Antony
No, that shouldn’t be the case. The lock should only occur at the end of the process when the high watermark is reset, which can potentially be performed later by only doing an initial shrink space compact command (which doesn’t do the high watermark reset and subsequent freeing of storage). An alter index shrink space command should hang, but not if you’re using the compact clause.
LikeLike
Hi Richard,
Thanks for insight. Recently we did index rebuild (from unusable state) on our OLTP (5 node RAC) and our index rebuild took a very long time. Majority of the time was spent on “library cache lock” and concurrency was bit high during the change. I got your point but I am not able to find why the session doing “alter index … rebuild online” acquired library cache lock. Would you be able to explain this?
Thanks,
Anjul
LikeLike
Hi Anjul
Rebuilding indexes have cause issues within the library cache and there also are a number of bugs associated with this. I would check MOS and see if there are any reported issues on your version of the database.
LikeLike
Hi Richard
Thanks for insightful article …..
Are these locking implications been resolved in 12C .
LikeLike
Hi Aasthasinha
No, the behavior is the same with 12c.
LikeLike
[…] discussed previously locking issues associated with the creation of indexes. Although things have changed and improved over the years, […]
LikeLike