Re: enqueue waits -- CI
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 -- - - CF68 0 CI 1117884 CU 1797012 DL 109 0 DR 102 0 DX 6219 0 IS72 0 MR 140 0 RT 1 0 SQ 2472 5 SS 1 0 ST 320734 TM278918 5 TS 4655 0 TX21005757 UL 3500 0 US 30496 0 WL10 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 104TX 6 p2=196736 p3=52393 ela=301 305TX 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
RE: enqueue waits -- CI (still remains)
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 -- - - CF68 0 CI 1117884 CU 1797012 DL 109 0 DR 102 0 DX 6219 0 IS72 0 MR 140 0 RT 1 0 SQ 2472 5 SS 1 0 ST 320734 TM278918 5 TS 4655 0 TX21005757 UL 3500 0 US 30496 0 WL10 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 104TX 6 p2=196736 p3=52393 ela=301 305TX 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
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. Eventtotal_waits time_waited pipe get910593342266184 Thank you. DC - Original Message - To: Diego Cutrone [EMAIL PROTECTED]; Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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:[EMAIL PROTECTED]] 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 104TX 6 p2=196736 p3=52393 ela=301 305TX 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
RE: enqueue waits -- CI
Hi Diego, I don't know of any way in which using DBMS_PIPE might be related to CI enqueue waits. However, even if there is something to their suggestion, if your ID values are 0 and 5 then the CI call you are waiting for is one of the reuse block range calls. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 18 May 2001 5:56 To: Multiple recipients of list ORACLE-L 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. Eventtotal_waits time_waited pipe get910593342266184 Thank you. DC - Original Message - To: Diego Cutrone [EMAIL PROTECTED]; Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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:[EMAIL PROTECTED]] 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 104TX 6 p2=196736 p3=52393 ela=301 305TX 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
RE: enqueue waits -- CI
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- Sent: Thursday, 17 May 2001 4:11 To: Multiple recipients of list ORACLE-L 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 104TX 6 p2=196736 p3=52393 ela=301 305TX 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
