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:
1) 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: [EMAIL PROTECTED]
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: [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).