RE: Application waiting for a user lock.
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.)
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Mladen Gogala [dmarc-noreply_at_freelists.org] Sent: 17 February 2015 03:43
To: oracle-l_at_freelists.org
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
http://mgogala.freehostia.com
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 17 2015 - 11:05:02 CET