Hi to you all,
High enqueue locks.
This morning before I got to work there were a group of users who
appeared to be hanging at times when they should have been performing
updates to certain tables.
I tracked it down to what I suspect is high enqueue lock times. [details are
below. I don't normally see enqueue high in v$session_event or
v$system_event].
I've used Steve's scripts enqueue_locks.sql and enqueue_stats.sql but they
don't tell me anything that I can understand :-(
By now the sessions that were affected have logged out or been
terminated, so I can't put a 10046 level 8 trace on them.
Can I see what the enqueue waits were referring to?
Thanks in advance,
Mike.
Details of findings:
1 select event, s.username , time_waited/100,total_waits, total_timeouts
2 from v$session_event e, v$session s
3 where s.sid= e.sid
4 and time_waited > 100
5 and event like '%enq%'
6* order by time_waited desc ;
Total
Event USERNAME TIME_WAITED/100 Waits Timeout
-------------------------- ---------- --------------- --------- -------
enqueue A 5498.45 1787 1787
enqueue B 3505.52 1140 1140
enqueue C 3303.44 1078 1071
enqueue D 209.89 69 69
enqueue E 63.29 21
21
enqueue F 16.17 14
4
6 rows selected.
and using Steve's resource_waiters script:
SQL> @resource_waiters
Event name [buffer busy waits] enqueue
SID PROGRAM TIME_WAITED AVERAGE_WAIT
---- ------------------------------ ----------- ------------
All Disconnected Sessions 7720431 306.021346
78 f45run@scc-corp01 (TNS V1-V2) 20989 20989
ARC0 oracle@scc-corp01 (ARC0) 2 .25
1 select * from v$system_event
2 where time_waited > 0
3* order by time_waited desc ;
Total Time Waitd
Average
Event Waits Timeout In Hndrds
Time
------------------------------------- --------- ------- -----------
-----------
SQL*Net message from client ######### 0 3271701695
30.397
rdbms ipc message 1875169 819790 422614554
225.374
slave wait 2213312 ####### 167923522
75.870
pipe get 260819 249480 126637278
485.537
pmon timer 277326 277293 85337384
307.715
smon timer 2783 2776 85322173
30658.345
enqueue 25297 25128 7741422
306.021
db file sequential read 48265252 0 5125270
.106
io done 820132 22440 2847667
3.472
db file scattered read 1677976 0 1585987
.945
log file parallel write 575601 2 1276956
2.218
log file sync 347401 1089 922192
2.655
Mike Jenner
Database Administrator
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jenner Mike
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).
