Oracle-L: RE: Large enqueue waits.

From: Jenner Mike <M.Jenner_at_southampton.gov.uk>
Date: Tue, 11 Jun 2002 03:18:19 -0800
Message-ID: <F001.0047A019.20020611031819@fatcity.com>

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?

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

--

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