enqueue waits

From: Diego Cutrone <dcutrone_at_afip.gov.ar>
Date: Tue, 15 May 2001 12:06:09 -0700
Message-ID: <F001.00303DBD.20010515105020@fatcity.com>

Hi List,
I'm trying to identify the possible cause of contention in a database:
Oracle 7.3.4.0.0 // HP-UX 10.20 //
FINANCIALS
As far I can see, event "enqueue" is on top (followed by some buffer busy waits)
<FONT face=Arial

size=2>EVENT                          

TIME_WAITED 

AVERAGE_WAIT------------enqueue                             

        854176     3746.39
(why is the average wait so high?)buffer busy

waits                         

292770          1.53
Enqueue Stats

---

TY     
GETS     WAITS-- ---------
---------CF       

68         
0CI     11178        
84CU     17970        
12DL       
109         
0DR       
102         
0DX      
6219         
0IS        
72         
0MR       
140         
0RT         
1         
0SQ      
2472         
5SS         
1         
0ST      
3207        34TM    
278918         
5TS      
4655         0TX    
210057        
57UL      
3500         
0US     
30496         

0WL       
10         018 rows
selected.
Now, in order to get further information about this wait, I've been tracing (for some reasonable time) some sessions (session in which I detected enqueue waits).
This is a "sample" of what I got :
<FONT face=Arial

size=2>****************************************************

/u02/oracle/admin/FNCL/udump/ora_26851.trc:WAIT #3: nam='enqueue' ela= 0 p1=1128857606 p2=0 p3=5/u02/oracle/admin/FNCL/udump/ora_26851.trc:WAIT #3: nam='enqueue' ela= 0 p1=1128857606 p2=0 p3=5/u02/oracle/admin/FNCL/udump/ora_26851.trc:WAIT #3: nam='enqueue' ela= 0 p1=1128857606 p2=0 p3=5/u02/oracle/admin/FNCL/udump/ora_26554.trc:WAIT #38: nam='enqueue' ela= 302 p1=1415053318 p2=196736 p3=52393
<FONT face=Arial

size=2>****************************************************

From this info I got the following
summary:
 (a)  (b)  (c)   

  1    T[   6

p2=983149 p3=6796 ela=175   2   T[    6 
p2=196736 p3=52393 ela=300   2   T[    6 
p2=393276 p3=50281 ela=300   3   CK  6 p2=0 
p3=5 ela=3   8   CK  6 p2=0 p3=5 

ela=1   9   T[    6 p2=983149 p3=6796

ela=301  12  CK   6 p2=0 p3=5 ela=0  
19  T[    6 p2=262223 p3=53352 ela=301  
75  T[    4 p2=524391 p3=50022 ela=301  
75  T[    6 p2=720923 p3=5194 
ela=301 104 T[    6 p2=196736 p3=52393 
ela=301 305 T[    6 p2=393276 p3=50281 

ela=301
where (a) is the total amount of equal
entries in the dumps (number of times it appears the same entry in the dumps), say a "sort -nr | uniq -c"... ,(b) is the LOCK TYPE (CF,CI,etc) and (c) is the LOCK MODE (ej: MODE 6=Exclusive lock).
Now, my questions are:
1) I couldn't find "T[ " LOCK TYPE. What is this? am I getting it wrong?, this is the query I used  select
chr(bitand(&&p1,-16777216)/16777215)||chr(bitand(&&p1,16711680)/63365) "Lock", to_char(bitand(&&p1,65535)) "Mode" from dual;
2) What does P2 and P3 mean? Can someone send me some information about it.
3) How would you interpret this information and what can be done in order to eliminate (or at least minimize) enqueue locks in this database? 
Thanks.

Received on Tue May 15 2001 - 14:06:09 CDT