Thanks for the TX information Jeffery. But my question about CI enqueue still remains. Here's the query to get enqueue waits statistics, it's from Steve Adams' site. select q.ksqsttyp type, q.ksqstget gets, q.ksqstwat waits from sys.x_$ksqst q where q.ksqstget > 0 / thanks again ----- Original Message ----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Thursday, May 17, 2001 5:55 AM > 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). -- 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).
