Oracle-L: RE: Enqueue Waits in Oracle Financials

From: Jay Hostetter <jhostetter_at_decommunications.com>
Date: Mon, 30 Dec 2002 05:43:51 -0800
Message-ID: <F001.005241E5.20021230054351@fatcity.com>

John,

I know this is an old topic, but Oracle Support proposed a patch and we finally got it tested. It looks like this enqueue wait goes away with the application of concurrent processing rollup patchset C 2385942.

Thanks,

Jay Hostetter
Oracle DBA
D. & E. Communications
Ephrata, PA USA

>>> john.kanagaraj_at_hds.com 12/05/02 07:39PM >>>
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:jhostetter_at_decommunications.com]
>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
>

<cut>

**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.net
-- 
Author: Jay Hostetter
  INET: jhostetter_at_decommunications.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).

Received on Mon Dec 30 2002 - 07:43:51 CST