Re: Large enqueue waits.

From: Jared Still <jkstill_at_cybcon.com>
Date: Mon, 10 Jun 2002 07:53:25 -0800
Message-ID: <F001.00478FC3.20020610075325@fatcity.com>

Mike,

Enqueues are locks on database objects.

When it happens again, you can check to see what objects are being waited on.

select

	s.username username,
	e.event event,
	s.sid,
	e.p1text,
	e.p1,
	e.p2text,
	e.p2,
	e.wait_time,
	e.seconds_in_wait,
	e.state

from v$session s, v$session_wait e
where s.username is not null

	and s.sid = e.sid
	-- skip sqlnet idle session messages
	and e.event not like '%message from client'

order by s.username, upper(e.event)
/

Jared

On Monday 10 June 2002 07:43, Jenner Mike wrote:
> 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: Jared Still
  INET: jkstill_at_cybcon.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).

Received on Mon Jun 10 2002 - 10:53:25 CDT