Oracle-L: RE: Large enqueue waits.

From: Freeman, Robert <Robert_Freeman_at_csx.com>
Date: Mon, 10 Jun 2002 07:23:27 -0800
Message-ID: <F001.00478F2F.20020610072327@fatcity.com>

Did you look to see if you had any blocking locks occurring at this time?

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