RE: Enqueue Waits in Oracle Financials

Jay,
 You may want to refer to Metalink Doc Id #29787.1 and 34566.1. Those will explain what p2, p3 are in an enqueue wait. Unfortunately, those will not discuss p2, p3 for UL :(  I know nothing about Oracle Apps (11i). May be John K. could comment on that.

• Kirti

-----Original Message-----
Sent: Thursday, December 05, 2002 10:25 AM To: Multiple recipients of list ORACLE-L

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 certa! in!
!
!

 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: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

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: ListGuru_at_fatcity.com (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).