RE: Application waiting for a user lock.

From: Jonathan Lewis <>
Date: Tue, 17 Feb 2015 10:05:02 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D92827231F_at_EXMBX01.thus.corp>

PL/SQL lock timer waits come from calls to dbms_lock.sleep, and the only way they could increase CPU is if some other process is polling a resource that that sleeper is holding to see if it is free. Having said that, the dbms_alert package has a polling implementation that applies in two possible circumstances (waitany calls, and RAC) which might have some relevance here as it makes use of dbms_lock in the polling mechanism. (I don't think that's relevant here given the (apparent) 60 second sleep) but you might as well do a quick check.)

Jonathan Lewis

From: [] on behalf of Mladen Gogala [] Sent: 17 February 2015 03:43
Subject: Application waiting for a user lock.

The wait events look like this:

Event                                            Waits    Time (s)   (ms)   Time

----------------------------------------- ------------ ----------- ------ ------
PL/SQL lock timer 60 3,601 60012 52.0 CPU time 3,233 46.7 db file sequential read 97,875 75 1 1.1 control file sequential read 13,019 5 0 .1 log file parallel write 1,344 4 3

Obviously, the application is using DBMS_LOCK. Is there anything that can be done to make it cheaper? Not only is the whole thing horribly slow, it also devours vast amounts of CPU. I cannot go into the source, it's a 3rd party application and someone discovered the keyword "WRAPPED". I've been looking for a hidden instance parameter which would change the DBMS_LOCK behavior and make it cheaper. Any ideas?


Mladen Gogala
Oracle DBA

-- Received on Tue Feb 17 2015 - 11:05:02 CET

Original text of this message