Hi Diego,
If lock type is 'TX', rollback segment number = trunc(p2/65536)
and slot number = p2 - 65536*trunc(p2/65536).
May I know which table you are looking at to get the following
detailed info about enqueue waits?
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.
Thanks
Jeffery
Diego Cutrone wrote:
> 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 <[EMAIL PROTECTED]>
> 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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Jeffery W
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
