jump to navigation

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 ;)

About these ads

Comments»

1. njethwa - February 11, 2008

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

2. Richard Foote - February 13, 2008

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 ?

3. Robert - March 25, 2008

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

4. Richard Foote - March 26, 2008

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 …

5. Robert Klemme - April 2, 2008

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. :-)

6. Richard Foote - April 2, 2008

Hi Robert

The only problem with common sense is that it’s sometimes not quite as common as the name suggests ;)

7. Robert Klemme - April 2, 2008

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.

8. Richard Foote - April 3, 2008

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.

9. Sharma - August 9, 2010

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

Richard Foote - August 10, 2010

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

10. John - November 9, 2010

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.

Richard Foote - November 10, 2010

Hi John

The table will be exclusively locked throughout the whole index rebuild process. So pick your indexes and pick your time wisely.

John - November 11, 2010

Thanks for the feedback. I suspected this was the case but just required confirmation.

11. antony - November 10, 2012

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

Richard Foote - December 4, 2012

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.


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

Follow

Get every new post delivered to your Inbox.

Join 1,715 other followers

%d bloggers like this: