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