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:
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: John Kanagaraj
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).
