Home » RDBMS Server » Server Administration » Locking issue during build and online option to avoid issues (Oracle 11.2.0.4 on Linux)
Locking issue during build and online option to avoid issues [message #636476] Sat, 25 April 2015 08:24 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi Experts,

In our suite of applications, the schemas are shared between several applications and even when there is a deployment of one app, the other app remains up. In this process we are facing some locking issues. For example when creating indexes, I am getting the error 'resource busy and acquired with no wait'. This post is specific to this point about error when creating indexes (changing the app design etc. is out of scope for me currently.)

I was checking the 'online' option of creating indexes and wondering if that helps. It seems promising to me. When we have large tables (50 million plus and index creation on them takes about 5 minutes and at the same time we have another app doing short transactions -that commit in 5 seconds, then creating index 'with online option' may help - is what I am wondering.

here is the sample of what I tried out:


-- first create a table with a few million rows:

SET SERVEROUTPUT ON

DECLARE
   v_sql   VARCHAR2 (200);
BEGIN
   v_sql := 'create table test as select * from dba_objects';

   EXECUTE IMMEDIATE v_sql;

   FOR i IN 1 .. 5
   LOOP
      v_sql := 'insert into test select * from test';

      EXECUTE IMMEDIATE v_sql;

      COMMIT;
   END LOOP;
END;
/

--Now in session 1:
---------


create index ti1 on test(OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,
CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE)
online;

session 2:
------------
DELETE FROM TEST WHERE ROWNUM<1000;

commit;

DELETE FROM TEST WHERE ROWNUM<40200;
rollback;

--etc.


I find that the deletes etc. in the second session gets done (they are following by commit and they take a few seconds) with this 'create index online' option.Also the 'create index' option does not error out with the ORA- error of 'resource busy...'. So is this a better way to create indexes to avoid the locking issues? Note that is is fine for us to have index creation take more time what is important is that they don't fail during critical production deployments.

Are there any other implications (-other than that it takes more time to create the indexes with the 'online option'), that one should be aware of? Any known issues or are there any other glitches with creating indexes online?

I will be thankful for an opinion on it.

Thanks,
OrauserN

[Updated on: Sat, 25 April 2015 09:59] by Moderator

Report message to a moderator

Re: Locking issue during build and online option to avoid issues [message #636477 is a reply to message #636476] Sat, 25 April 2015 08:32 Go to previous messageGo to next message
John Watson
Messages: 7397
Registered: January 2010
Location: Global Village
Senior Member
First, you need to be aware of the licensing implications: online index creation is legal only with Enterprose Edition licences.

Second, have you attempted to start the index creation when there is already a transaction against the table? You'll find a problem. CREATE INDEX...ONLINE needs a short table lock, and if it is a busy table, you may never get it. The way around this is to quiesce the database for a few seconds, start the index build, and unquiesce. Your users ay nit even notice.
Re: Locking issue during build and online option to avoid issues [message #636478 is a reply to message #636477] Sat, 25 April 2015 08:37 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thank you John for your review!

Following is what I have to say on it:
1. licensing implications: Yea, we are good here, have the required licences.
2. "CREATE INDEX...ONLINE needs a short table lock, and if it is a busy table, you may never get it." Luckily our tables are not too busy. They are not constantly DML'ed or even contantly queried. So in all likelyhood, we should be able to get that short lock.

Given these, is it a good option to try out , or are there some other known 'bugs or side effects or other such issues'? I noticed that oracle documentation has this note
Quote:
"Keep in mind that the time that it takes on online index build to complete is proportional to the size of the table and the number of concurrently executing DML statements. Therefore, it is best to start online index builds when DML activity is low."


This is fine with us - we are creating indexes during deployments at night when other apps are up but their 'activeness' is low -they are not very heavily used.

Thanks a lot again!

[Updated on: Sat, 25 April 2015 08:57]

Report message to a moderator

Re: Locking issue during build and online option to avoid issues [message #636479 is a reply to message #636478] Sat, 25 April 2015 11:34 Go to previous message
orausern
Messages: 817
Registered: December 2005
Senior Member
Just FYI...the question got discussed on OTN also at this link:

https://community.oracle.com/message/13026558#13026558

And my queries are resolved. thanks a lot again!
Previous Topic: Delete repository tables in Oracle data modeler
Next Topic: Oracle 11g Install
Goto Forum:
  


Current Time: Wed May 23 07:55:04 CDT 2018