enqueue waits

[Diego Cutrone]

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) 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         0 CI     11178        84 CU     17970        12 DL       109         0 DR       102         0 DX      6219         0 IS        72         0 MR       140         0 RT         1         0 SQ      2472         5 SS         1         0 ST      3207        34 TM    278918         5 TS      4655         0 TX    210057        57 UL      3500         0 US     30496         0 WL        10         0 18 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 : **************************************************** /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 **************************************************** 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.