RE: enqueue waits

From: Mohan, Ross <MohanR_at_STARS-SMI.com>
Date: Tue, 15 May 2001 13:17:30 -0700
Message-ID: <F001.003040D9.20010515120558@fatcity.com>

see p2text and p3text for more.

Sounds like a one-time long-held table/row lock to me.

If you can, bounce the instance and recheck for reoccurence

hth

Ross "I wish I could do statistics" Mohan

-----Original Message-----
Sent: Tuesday, May 15, 2001 2:50 PM
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: Mohan, Ross
  INET: MohanR_at_STARS-SMI.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:17:30 CDT