RE: enqueue waits -- CI

Thanks for answering Unal, John.

John, you were right about the query. I've corrected it. And I'm not getting "T[" and "CK" anymore,now I get TX and CI. I've also done further investigation and I also know now what p2 and p3 mean.

This is the updated data: (from the dumps)

count     TYPE  MODE
 13          CI          6      p2=0 p3=5 ela=0
  19         TX         6     p2=262223 p3=53352 ela=301
  75         TX         4     p2=524391 p3=50022 ela=301
  75         TX         6     p2=720923 p3=5194 ela=301
 104        TX         6     p2=196736 p3=52393 ela=301
 305        TX         6     p2=393276 p3=50281 ela=301
------------------------------------------------

Now, how can I get the rollback segment number involved in the TX ? (I know its from p2 and p3, but how?) --just curious. I'll also take John advise, and I'll try to identify the locking session(s).

Now, although CI enqueue waits (cross instance call invocation) are brief on this sample, Sometimes it's not.
So I'm trying to understand what it means. According to p2 and p3 flags, they are indicating "Flush buffers for reuse as new class", that means that a session needs a buffer (in the shared pool I think) and it has to flush some others in order to get space. am I correct?. I've also read a metalink document (1020355.102). According to this, one possible cause is that my application is using dbms_pipe extensively (it may be right, I've seen event "pipe get" very high). The suggested solution is to increase the shared_pool. I can't access the other documents mentioned in the paper.

Can someone explain to me what means this CI enqueue and how can I reduce it.
TIA

• Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Tuesday, May 15, 2001 6:16 PM

> Hi Diego,
>
> Without going into details, an 'enqueue' wait is mostly due to a
> user/program initated transaction lock and I see it a lot in Financial
> databases (I see you are on 10.7?). I deduce you were looking at
> V$SYSTEM_EVENT - If a user locked one row (probably queried a row in
Forms,
> inadvertly changed one character which issues a 'SELECT for UPDATE, thus
> locking that row) and anotehr user (through a form or a report) needs to
> perform DML on that particular row, then (I believe) you will clock up
time
> against the 'enqueue' event.
>
> What I would suggest is that you use the following query to determine if
> someone is locking someone else out:
>
> select event, count(*) from v$session_wait
> group by event
>
> If you see the 'enqueue' event in this list, some process is probably
> waiting on a lock... You can then trace the user/process via Lock
detection
> scripts (see Metablink) and kill the blocking process. You could also
query
> from sys.dba_waiters which will present an easier picture in this case..
>
> As far as the SQL goes, see below:
>
> select
> chr(bitand(&&p1,-16777216)/16777215)||chr(bitand(&&p1,16711680)/63365)
> "Lock", <----- I believe the value is '65535', rather than 63365)
> to_char(bitand(&&p1,65535)) "Mode"
> from dual
>
> You can learn a lot from the Oracle 8.1 Reference Manual - Appendix A.
> "Oracle Wait Events" and App B "Oracle Enqueue Names". While they are
> applicable for 8.1, most of the info is valid for 7.3 as well...
>
> Please let us know if you need additional info.
>
>
> John Kanagaraj (A long time member of the "Always look at v$session_wait
> first" camp)
> Oracle Applications DBA
> Hitach Data Systems, Santa Clara
> Work : (408) 970 7002
>
> -----Original Message-----
> Sent: Tuesday, May 15, 2001 11:50 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> 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.
>
>
>
>
>
>
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: John Kanagaraj
> INET: john.kanagaraj_at_hds.com
>
> 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: Diego Cutrone
  INET: dcutrone_at_afip.gov.ar

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