OraFAQ Forum: Server Administration » DBMS_LOCK_ALLOCATED

Home » RDBMS Server » Server Administration » DBMS_LOCK_ALLOCATED (Oracle 10g 10.2.0.3.0 Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBMS_LOCK_ALLOCATED [message #530090]

Fri, 04 November 2011 11:16

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

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530093 is a reply to message #530090]

Fri, 04 November 2011 11:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530095 is a reply to message #530093]

Fri, 04 November 2011 11:50

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?

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530096 is a reply to message #530095]

Fri, 04 November 2011 11:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530102 is a reply to message #530096]

Fri, 04 November 2011 12:04

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?

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530103 is a reply to message #530102]

Fri, 04 November 2011 12:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530104 is a reply to message #530102]

Fri, 04 November 2011 12:11

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

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530108 is a reply to message #530104]

Fri, 04 November 2011 12:29

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

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530220 is a reply to message #530108]

Mon, 07 November 2011 05:08

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.

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530262 is a reply to message #530220]

Mon, 07 November 2011 07:34

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

I suppose, your Russian isn't so good as your English Wink

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

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

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

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530315 is a reply to message #530312]

Mon, 07 November 2011 10:59

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.

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530316 is a reply to message #530315]

Mon, 07 November 2011 11:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530321 is a reply to message #530316]

Mon, 07 November 2011 11:20

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

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530348 is a reply to message #530321]

Mon, 07 November 2011 15:35

LNossov
Messages: 318
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

Report message to a moderator

Re: DBMS_LOCK_ALLOCATED [message #530464 is a reply to message #530348]

Tue, 08 November 2011 08:01

Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom

Member

Thanks a lot Leonid. I will check the options and see if they work.

Report message to a moderator

Previous Topic:	Database Rename
Next Topic:	space reclaim

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 23 11:48:28 CST 2025