RE: Large enqueue waits.

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:anjo_at_oraperf.com]
> 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_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 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: 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).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Freeman, Robert
> > INET: Robert_Freeman_at_csx.com
> >
> > 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).
> >
> > --
> > 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).
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Anjo Kolk
> INET: anjo_at_oraperf.com
>
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

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).