Identify the enqueue type by: select * from x$ksqst where ksqstwat != 0;
You have to be sys for that ..... Anjo. Jenner Mike wrote: > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk 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).
