Home » RDBMS Server » Server Administration » DBMS_LOCK_ALLOCATED (Oracle 10g 10.2.0.3.0 Linux)
DBMS_LOCK_ALLOCATED [message #530090] Fri, 04 November 2011 11:16 Go to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
HI,
I googled a bit and got to know that it is a part of Oracles user lock management. But I still have more queries.

There is this query that crops up in our AWR report and has an elapsed time of about 4.5s. It typically gets executed around 250 times per hour.

DELETE DBMS_LOCK_ALLOCATED WHERE EXPIRATION < SYSDATE


1. I would expect it to be a background housekeeping job that Oracle might be triggering. Hopefully it is not something that would add to the elapsed time of any query (even DBMS_LOCK for that matter) invoked by the application.

2. What factors influence the number of times it gets executed? It 4.5 seconds per execution normal? I feel its a lot and it means there it has too much rows to delete!

3. Coincidentally, when the elapsed time of this is higher in a particuar snap_id, various operations are also slow! Please note that the PLSQL Lock sleep time for that snap is not too high either. Does that mean that the locks obtained using DBMS_LOCK is not accounted anywhere in the AWR?

Cheers,
Rags
Re: DBMS_LOCK_ALLOCATED [message #530093 is a reply to message #530090] Fri, 04 November 2011 11:43 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
Interesting!
I had NEVER heard of this structure until this thread.
The DB on my laptop (V11.2) has 73 entries going back as far as 2009-08-25 00:33:35
I suspect that it some application on your DB Server that is issuing posted DELETE.
Can you AUDIT this object to determine who/what issues DELETE?
DBMS_LOGMNR might also provide some additional clues.
Re: DBMS_LOCK_ALLOCATED [message #530095 is a reply to message #530093] Fri, 04 November 2011 11:50 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Hi Blackswan,
I could not quite understand, can u elaborate what a "posted Delete" is?
Re: DBMS_LOCK_ALLOCATED [message #530096 is a reply to message #530095] Fri, 04 November 2011 11:52 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
>I could not quite understand, can u elaborate what a "posted Delete" is?
your posted DELETE statement below

DELETE DBMS_LOCK_ALLOCATED WHERE EXPIRATION < SYSDATE
Re: DBMS_LOCK_ALLOCATED [message #530102 is a reply to message #530096] Fri, 04 November 2011 12:04 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Haha... Alright.

Log miner I think is something that will work only for that particular session. I wont be able to change the application to set this up really. Is there any other way?
Re: DBMS_LOCK_ALLOCATED [message #530103 is a reply to message #530102] Fri, 04 November 2011 12:11 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
>There is this query that crops up in our AWR report and has an elapsed time of about 4.5s.
Over how much wall clock? (1 hour?)
If this 4.5 seconds went to zero, would any one notice the change?

What problem are you really trying to solve?
post SQL & results that show problem exists.
Re: DBMS_LOCK_ALLOCATED [message #530104 is a reply to message #530102] Fri, 04 November 2011 12:11 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
For a moment I thought it is from the application but someone had found the same query in their application. Check out this asktom link, and search for that posted deleted query.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5671539468597
Re: DBMS_LOCK_ALLOCATED [message #530108 is a reply to message #530104] Fri, 04 November 2011 12:29 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
The problem is one of the PLSQL API (coming from a third party vendor product) is slow. It happens only on Production and we could not replicate it on test server. We even enabled a session trace and called that API on Live but it was fairly quick then. Hence it is still a mystery why it is slow.

We know that that API locks certain objects though we dont know if it uses DBMS_LOCK. The application is designed in such a way that every session calls this PLSQL 4 to 5 times in a single session. We doubt that the API calls the DBMS_LOCK which internally does this DELETE but jsut once every session. The reason for this doubt is the number of executions of of DELETE statement is about one-fourth to one-fifth the number of the API in a particular snap. The elapsed time of the query is very close to the elapsed time of this PLSQL API.

I understand it might be completely unrelated but I just could not think of any other reason. My intention of raising this topic is to know more about where that DELETE statement comes into picture and the implications behind it taking 4.5 seconds.

Cheers,
Rags
Re: DBMS_LOCK_ALLOCATED [message #530220 is a reply to message #530108] Mon, 07 November 2011 05:08 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
I just happened to bump into this russian link and it stuck to me that may be some applications are developed that way. That means it might not a query triggered by Oracle then!!

http://www.sql.ru/forum/actualthread.aspx?tid=807901&pg=3

Our application is a third party vendor product. I have raised a query with them.. lets see what they say.. I will keep this post updated.
Re: DBMS_LOCK_ALLOCATED [message #530262 is a reply to message #530220] Mon, 07 November 2011 07:34 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
I suppose, your Russian isn't so good as your English Wink. The Russians found this Delete in the unwrapped code of dbms_lock. So dbms_lock itself uses this command.


[Updated on: Mon, 07 November 2011 07:40] by Moderator

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530312 is a reply to message #530262] Mon, 07 November 2011 10:47 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
My goodness! I translated that page using Google translator and thats how I understood! Very Happy

Brilliant Leonid, so this code is for sure coming from Oracle then!

Are you able to explain more details from that page? Is it that under certain conditition that query is called by the DBMS_LOCK package itself which actually means that the time taken by that query adds to the time taken by the application code (which calls the DBMS_LOCK)? If that is the case, it clearly explains the behaviour of our application!

Is there any suggestions they have given in that page?

Cheers,
Rags
Re: DBMS_LOCK_ALLOCATED [message #530315 is a reply to message #530312] Mon, 07 November 2011 10:59 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Isn't it a bit weird that some random transaction ends up paying the price for the locks created by other transaction that might be completely unrelated? It makes it tough to get to the bottom of an issue.
Re: DBMS_LOCK_ALLOCATED [message #530316 is a reply to message #530315] Mon, 07 November 2011 11:05 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
>There is this query that crops up in our AWR report and has an elapsed time of about 4.5s.
Over how much wall clock? (1 hour?)
If this 4.5 seconds went to zero, would any one notice the change?
Re: DBMS_LOCK_ALLOCATED [message #530321 is a reply to message #530316] Mon, 07 November 2011 11:20 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Hi Blackswan,
It typically takes about 4.5 seconds per execution. And we typically see around 250 executions per wall clock hour.

When I started the topic, I was not sure whether the elapased time becoming 0s will make any difference or not. I just had some doubts if that has anything to do with the slowness of our application.

But with the way things have progressed, it looks like it is definitely going to make a difference to the application. If that query adds up to the elapsed time of our application code (as it looks like at the moment), it reducing to zero will make quite a big difference.

Let me add some stats for todays production from 9 AM to 5 PM BST.

There is a PLSQL which we know acquires lock using DBMS_LOCK, that got executed 17357 times with an average of 4.53 seconds.
And this particular DELETE statement in the same window got executed 2063 times with an average of 7.67 seconds.

If you assume uniform load,my calculation says that this query contributes to 20% of the total elapsed time of that PLSQL!. And the previous experience is that whenever we see the elapsed time of this PLSQL going up, this query elapsed time is also higher

Cheers,
Rags



Re: DBMS_LOCK_ALLOCATED [message #530348 is a reply to message #530321] Mon, 07 November 2011 15:35 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
Your table DBMS_LOCK_ALLOCATED should be big (the HWM has to be high). The problematic delete makes a full scan on this table and can be expensive. So you can manually rebuild this table as described in the Doc ID 1054343.6 (I suppose, you need a time-out for that). Alternatively you could create an index on the column EXPIRATION, but I don't no, if it is a supported solution (you can open a service request at Oracle and ask about it).

Regards
Leonid
Re: DBMS_LOCK_ALLOCATED [message #530464 is a reply to message #530348] Tue, 08 November 2011 08:01 Go to previous message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Thanks a lot Leonid. I will check the options and see if they work.
Previous Topic: Database Rename
Next Topic: space reclaim
Goto Forum:
  


Current Time: Tue Sep 30 23:59:01 CDT 2014

Total time taken to generate the page: 0.15258 seconds