Jay,
Does this come from the alert manager? Do you have any of those new-fangled
11i modules (or should I call the 'mangled'!!). You could use the script
below (adapted from Govind who posted this a few days back)
set pages 100
column sid_serial format a10 heading "Sid/Ser#"
column username format a15 heading "DB/OSUser"
column start_time format a18 heading "StartTime"
column mins_pending format 999 heading "Mins"
column used_ublk format 9999 heading "Blks"
column name format a10 heading "Rbs Name"
column status format a12 heading "Status"
select sid || '/' || serial# sid_serial, username || '/' || osuser username,
substr(t.start_time,1,18) start_time,
round( ( sysdate - TO_DATE( start_time, 'MM/DD/YY HH24:MI:SS')
) *24*60 ,0 ) mins_pending,
r.name, t.used_ublk ,
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status))) status
from v$transaction t, v$rollname r, v$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
order by t.start_time
/
If the OS user turns out to be 'applmgr' for any waiting TXN then pursue
this from the CM side. Otherwise, you can look at the Forms users. In any
case, are you using OAM (Oracle Applications Manager)?
John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002
Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **
>-----Original Message-----
>From: Jay Hostetter [mailto:[EMAIL PROTECTED]]
>Sent: Thursday, December 05, 2002 8:25 AM
>To: Multiple recipients of list ORACLE-L
>Subject: Enqueue Waits in Oracle Financials
>
>
>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: 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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Kanagaraj
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).
