RE: enqueue waits

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Tue, 15 May 2001 13:18:05 -0700
Message-ID: <F001.003042F8.20010515131628@fatcity.com>

Hi Diego,

Without going into details, an 'enqueue' wait is mostly due to a user/program initated transaction lock and I see it a lot in Financial databases (I see you are on 10.7?). I deduce you were looking at V$SYSTEM_EVENT - If a user locked one row (probably queried a row in Forms, inadvertly changed one character which issues a 'SELECT for UPDATE, thus locking that row) and anotehr user (through a form or a report) needs to perform DML on that particular row, then (I believe) you will clock up time against the 'enqueue' event.

What I would suggest is that you use the following query to determine if someone is locking someone else out:

select event, count(*) from v$session_wait group by event

If you see the 'enqueue' event in this list, some process is probably waiting on a lock... You can then trace the user/process via Lock detection scripts (see Metablink) and kill the blocking process. You could also query from sys.dba_waiters which will present an easier picture in this case..

As far as the SQL goes, see below:

select
chr(bitand(&&p1,-16777216)/16777215)||chr(bitand(&&p1,16711680)/63365) "Lock", <----- I believe the value is '65535', rather than 63365) to_char(bitand(&&p1,65535)) "Mode"
from dual

You can learn a lot from the Oracle 8.1 Reference Manual - Appendix A. "Oracle Wait Events" and App B "Oracle Enqueue Names". While they are applicable for 8.1, most of the info is valid for 7.3 as well...

Please let us know if you need additional info.

John Kanagaraj (A long time member of the "Always look at v$session_wait first" camp)
Oracle Applications DBA
Hitach Data Systems, Santa Clara
Work : (408) 970 7002

-----Original Message-----
Sent: Tuesday, May 15, 2001 11:50 AM
To: Multiple recipients of list ORACLE-L

Hi List,

I'm trying to identify the possible cause of contention in a database:

Oracle 7.3.4.0.0 // HP-UX 10.20 // FINANCIALS

As far I can see, event "enqueue" is on top (followed by some buffer busy waits)

EVENT                          TIME_WAITED  AVERAGE_WAIT
------------
enqueue                                     854176     3746.39 (why is the
average wait so high?)
buffer busy waits                         292770          1.53

Enqueue Stats

TY GETS WAITS
-- --------- ---------

CF        68         0
CI     11178        84
CU     17970        12
DL       109         0
DR       102         0
DX      6219         0
IS        72         0
MR       140         0
RT         1         0
SQ      2472         5
SS         1         0
ST      3207        34
TM    278918         5
TS      4655         0
TX    210057        57
UL      3500         0
US     30496         0
WL        10         0

18 rows selected.

Now, in order to get further information about this wait, I've been tracing (for some reasonable time) some sessions (session in which I detected enqueue waits).

This is a "sample" of what I got :

/u02/oracle/admin/FNCL/udump/ora_26851.trc:WAIT #3: nam='enqueue' ela= 0
p1=1128857606 p2=0 p3=5
/u02/oracle/admin/FNCL/udump/ora_26851.trc:WAIT #3: nam='enqueue' ela= 0
p1=1128857606 p2=0 p3=5
/u02/oracle/admin/FNCL/udump/ora_26851.trc:WAIT #3: nam='enqueue' ela= 0
p1=1128857606 p2=0 p3=5
/u02/oracle/admin/FNCL/udump/ora_26554.trc:WAIT #38: nam='enqueue' ela= 302
p1=1415053318 p2=196736 p3=52393

>From this info I got the following summary:

(a) (b) (c)

  1    T[   6 p2=983149 p3=6796 ela=175
   2   T[    6 p2=196736 p3=52393 ela=300
   2   T[    6 p2=393276 p3=50281 ela=300

   3 CK 6 p2=0 p3=5 ela=3
   8 CK 6 p2=0 p3=5 ela=1
   9 T[ 6 p2=983149 p3=6796 ela=301

  12  CK   6 p2=0 p3=5 ela=0
  19  T[    6 p2=262223 p3=53352 ela=301
  75  T[    4 p2=524391 p3=50022 ela=301
  75  T[    6 p2=720923 p3=5194 ela=301

104 T[ 6 p2=196736 p3=52393 ela=301
305 T[ 6 p2=393276 p3=50281 ela=301

where (a) is the total amount of equal entries in the dumps (number of times it appears the same entry in the dumps), say a "sort -nr | uniq -c"... ,(b) is the LOCK TYPE (CF,CI,etc) and (c) is the LOCK MODE (ej: MODE 6=Exclusive lock).

Now, my questions are:

I couldn't find "T[ " LOCK TYPE. What is this? am I getting it wrong?, this is the query I used

select
chr(bitand(&&p1,-16777216)/16777215)||chr(bitand(&&p1,16711680)/63365) "Lock",
to_char(bitand(&&p1,65535)) "Mode"
from dual;

2) What does P2 and P3 mean? Can someone send me some information about it.

3) How would you interpret this information and what can be done in order to eliminate (or at least minimize) enqueue locks in this database?

Thanks.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Tue May 15 2001 - 15:18:05 CDT