I noticed a lot of enqueue wait events in our 11i database. I ran some queries and
was able to determine the process that is incurring these waits. I dutifully did a
set event 10046 and examined the trace file. I've also queried v$lock. I've figured
out that this is a UL (user defined) wait. Now I'm stuck. I haven't figured out
exactly what we are waiting for. Although by monitoring the current SQL statement for
the offending process, I see that it does a SELECT FOR UPDATE in the
FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROGRAMS tables. This creates a TM lock,
which I see, but I don't think it explains the UL lock. I've seen examples on how to
interpret p1 for an enqueue lock, but not p2. I would appreciate a little guidance.
I believe that the offending process is the Internal manager, but I would like to
understand a little more about what is occurring. Is this a typical problem in 11i?
I guess the ICM may issue user defined locks, then just waits for a certain!
amount of time. I would guess that all 11i databases have a high number of enqueue
waits if this is the case. I am running 11.5.6 against 8.1.7 on Tru64.
Thank you,
Jay
Sample output from the trace:
WAIT #114: nam='enqueue' ela= 102 p1=1431044098 p2=1073807913 p3=0
WAIT #114: nam='enqueue' ela= 103 p1=1431044098 p2=1073807914 p3=0
WAIT #114: nam='enqueue' ela= 102 p1=1431044098 p2=1073807915 p3=0
So if I check out p1 I see a UL lock mode 2:
SQL> run
1 SELECT chr(bitand(1431044098,-16777216)/16777215)||
2 chr(bitand(1431044098, 16711680)/65535) "Lock",
3 to_char( bitand(1431044098, 65535) ) "Mode"
4* from dual
Lo M
-- -
UL 2
Sample output from v$lock for SID 14 (not at the exact same time as the lock shown
above):
ADDR KADDR Sid TY ID1 ID2 LMODE REQUEST
CTIME BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ----------
---------- ----------
000000040147E578 000000040147E5A0 14 TM 130213 0 2 0
78 0
0000000400B1B430 0000000400B1B450 14 UL 1073741851 0 6 0
33188 0
0000000400B16340 0000000400B16360 14 UL 1073807990 0 6 0
33158 0
I can see that there are quite a few UL waits:
SQL> run
1 SELECT ksqsttyp "Lock",
2 ksqstget "Gets",
3 ksqstwat "Waits"
4* FROM X$KSQST where KSQSTWAT > 0
Lo Gets Waits
-- ---------- ----------
TX 170144 59
UL 7275 6011
Other info:
SQL> SELECT *
FROM v$sysstat
WHERE cla 2 3 ss=4;
STATISTIC# NAME CLASS
VALUE
---------- ---------------------------------------------------------------- ----------
----------
22 enqueue timeouts 4
6729
23 enqueue waits 4
6297
24 enqueue deadlocks 4
1
25 enqueue requests 4
852617
26 enqueue conversions 4
27889
27 enqueue releases 4
845696
SQL> run
1 SELECT *
2 FROM v$system_event
3* WHERE event = 'enqueue'
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
-------------------- ----------- -------------- ----------- ------------
enqueue 6881 6520 732348 106.430461
>From a long query that joins v$process, v$session, v$session_event,
>fnd_concurrent_processes, fnd_concurrent_queues_vl and looks for enqueue wait events:
DB_PROCESS Sid MANAGER_OS P USER_CONCURRENT_QUEUE_NAME EVENT
TWS TT TW AVGW
---------- ----- ---------- - ------------------------------ ---------------
---------- ---------- ---------- ----------
1175422 92 1098457 A Workflow Manager (D&E) enqueue
1 0 1 1
1122160 13 1120706 A PO Document Approval Manager enqueue
6 4 1341 223.5
1121613 25 1121812 A INV Remote Procedure Manager enqueue
6 4 1424 237.333333
1119743 24 1122331 A INV Remote Procedure Manager enqueue
5 4 1523 304.6
1116804 14 1084357 A Internal Manager enqueue
6307 6289 644107 102.125733
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
D&E except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
