RE: Application waiting for a user lock.

From: Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>
Date: Tue, 17 Feb 2015 06:56:36 +0000
Message-ID: <2FE2AA1C5F8DEC478F58DF8DD32BA63701A869_at_HKWPIPXMB03C.zone1.scb.net>

Maybe it is multiple sessions running (requesting/waiting) concurrently (e.g. 60 sessions with 60second wait requests). Would the REQUEST result in CPU usage ?

Would a SLEEP call result in CPU usage ?

Hemant K Chitale

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman Sent: Tuesday, February 17, 2015 11:57 AM To: oracle-l_at_freelists.org
Subject: Re: Application waiting for a user lock.

Mladen,

The REQUEST procedure in the DBMS_LOCK package<http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_lock.htm#ARPLS66779> has a TIMEOUT parameter which by default is set to "indefinite", but can be set to a specified number of seconds. It appears that the developer set it to "60" from what you're showing here (i.e. 60 waits in 3,601 seconds).

Hope this helps...

-Tim

On 2/16/15 20:43, Mladen Gogala (Redacted sender mgogala_at_yahoo.com<mailto:mgogala_at_yahoo.com> for DMARC) wrote: 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

This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html.
--

http://www.freelists.org/webpage/oracle-l Received on Tue Feb 17 2015 - 07:56:36 CET