| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Large enqueue waits.
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
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_at_scc-corp01 (TNS V1-V2) 20989 20989
ARC0 oracle_at_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 51252702.655
.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
Mike Jenner
Database Administrator
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jenner Mike INET: M.Jenner_at_southampton.gov.uk 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 Mon Jun 10 2002 - 09:43:24 CDT
 |
 |