Anjo, So what does this mean?
select * from x$ksqst where ksqstwat != 0; ADDR INDX KS KSQSTGET KSQSTWAT -------- ---------- -- ---------- ---------- 4612E15C 69 CF 480 1 4612E1D4 84 CU 11709 5 4612F1B4 592 SQ 3577 9 4612F1CC 595 ST 4600 155 4612F294 620 TM 132223 4 4612F2EC 631 TX 68902 75 John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** > -----Original Message----- > From: Anjo Kolk [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, June 11, 2002 6:18 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: Large enqueue waits. > > > 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). > -- 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).
