RE: enqueue waits -- CI

As usual, "thanks" for the answer Steve.

I'll try to reduce the number of shrinks in the rollback segments, so this is going to impact on CI enqueue waits.

What do you think about Metalink DOC ID 1020355.102 recomendation?

(from my previous email)
"> 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 "pipe get" event very high). The suggested solution is
> to increase the shared_pool. I can't access the other documents mentioned
> in the paper."

Event                                total_waits   time_waited
pipe get                            910593        342266184

Thank you.
DC

• Original Message ----- To: Diego Cutrone <dcutrone_at_afip.gov.ar>; Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Thursday, May 17, 2001 1:53 AM

> Hi Diego,
>
> No, those are block buffers. When a segment is dropped, truncated or
shrunk
> (normally a rollback segment) then a "reuse block range" cross instance
call is
> needed to flush the unwanted blocks from cache. Similarly, before a
parallel
> direct read a "checkpoint block range" or "checkpoint object" cross
instance
> call is needed (otherwise changes made prior to the start of the query and
> committed but not yet flushed to disk could be missed by the direct
reads).
> These are "cross-instance" calls even in single-instance Oracle because
the code
> allows for the possibility of parallel server, and the DBWn processes in
all
> instance need to flush the cache in their own instances.
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/
> @ http://www.christianity.net.au/
>
>
> -----Original Message-----
> From: Diego Cutrone [mailto:dcutrone_at_afip.gov.ar]
> Sent: Thursday, 17 May 2001 4:11
> To: Multiple recipients of list ORACLE-L
> Subject: 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).

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