----- Original Message -----
Sent: Tuesday, May 15, 2001 9:50 PM
Subject: enqueue waits
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.
