Rob, Jared, all,
By the time I was able to investigate, the problem had disappeared.
No blocking locks and nothing unusual in v$session_wait by that time. I
guess I'll set up an automatic script to run perhaps every 10 mins that
checks and logs this sort of thing.
Unless anyone knows if this is remembered internally..
Mike Jenner
Database Administrator
-----Original Message-----
Sent: 10 June 2002 16:23
To: Multiple recipients of list ORACLE-L
Did you look to see if you had any blocking locks occurring at this time?
RF
Robert G. Freeman - Oracle8i OCP
Oracle DBA Technical Lead
CSX Midtier Database Administration
Author: Oracle9i New Features�
Mastering Oracle8i�����������������������
Clark Griswold: Eddie,�has anyone�ever told you that you're bad luck?
Cousin Eddie: Those were my mother's dying words. But I guess if your
body's covered in third degree burns, and your foot's caught in a bear
trap, you tend to start talkin' crazy.
-----Original Message-----
Sent: Monday, June 10, 2002 10:43 AM
To: Multiple recipients of list ORACLE-L
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Freeman, Robert
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).
--
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).
