Ynt: enqueue waits

From: unal-bilisim <danisment.unal_at_unal-bilisim.com>
Date: Wed, 16 May 2001 02:03:54 -0700
Message-ID: <F001.003048D2.20010516020554@fatcity.com>

Hi,

you can find the time spent in each enqueu by itrprof.
<BLOCKQUOTE
style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">

Original Message -----
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: Diego Cutrone To: <A href="mailto:ORACLE-L_at_fatcity.com" title=ORACLE-L_at_fatcity.com>Multiple recipients of list ORACLE-L Sent: Tuesday, May 15, 2001 9:50 PM Subject: enqueue waits

  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"... ,<FONT face=Arial   size=2>(b) is the LOCK TYPE (CF,CI,etc) and (c) is the LOCK MODE (ej: MODE   6=Exclusive lock).
  Now, my questions are:

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;
What does P2 and P3 mean? Can someone send me some information about it.
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 Wed May 16 2001 - 04:03:54 CDT