RE: Enqueue Waits in Oracle Financials
John, I know this is an old topic, but Oracle Support proposed a patch and we finally got it tested. It looks like this enqueue wait goes away with the application of concurrent processing rollup patchset C 2385942. Thanks, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 12/05/02 07:39PM Jay, Does this come from the alert manager? Do you have any of those new-fangled 11i modules (or should I call the 'mangled'!!). You could use the script below (adapted from Govind who posted this a few days back) set pages 100 column sid_serial format a10 heading Sid/Ser# column username format a15 heading DB/OSUser column start_time format a18 heading StartTime column mins_pending format 999 heading Mins column used_ublk format heading Blks column name format a10 heading Rbs Name column status format a12 heading Status select sid || '/' || serial# sid_serial, username || '/' || osuser username, substr(t.start_time,1,18) start_time, round( ( sysdate - TO_DATE( start_time, 'MM/DD/YY HH24:MI:SS') ) *24*60 ,0 ) mins_pending, r.name, t.used_ublk , decode(t.space, 'YES', 'SPACE TX', decode(t.recursive, 'YES', 'RECURSIVE TX', decode(t.noundo, 'YES', 'NO UNDO TX', t.status))) status from v$transaction t, v$rollname r, v$session s where t.xidusn = r.usn and t.ses_addr = s.saddr order by t.start_time / If the OS user turns out to be 'applmgr' for any waiting TXN then pursue this from the CM side. Otherwise, you can look at the Forms users. In any case, are you using OAM (Oracle Applications Manager)? John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Jay Hostetter [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 8:25 AM To: Multiple recipients of list ORACLE-L Subject: Enqueue Waits in Oracle Financials I noticed a lot of enqueue wait events in our 11i database. I ran some queries and was able to determine the process that is incurring these waits. I dutifully did a set event 10046 and examined the trace file. I've also queried v$lock. I've figured out that this is a UL (user defined) wait. Now I'm stuck. I haven't figured out exactly what we are waiting for. Although by monitoring the current SQL statement for the offending process, I see that it does a SELECT FOR UPDATE in the FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROGRAMS tables. This creates a TM lock, which I see, but I don't think it explains the UL lock. I've seen examples on how to interpret p1 for an enqueue lock, but not p2. I would appreciate a little guidance. I believe that the offending process is the Internal manager, but I would like to understand a little more about what is occurring. Is this a typical problem in 11i? I guess the ICM may issue user defined locks, then just waits for a certa! in! amount of time. I would guess that all 11i databases have a high number of enqueue waits if this is the case. I am running 11.5.6 against 8.1.7 on Tru64. Thank you, Jay Sample output from the trace: WAIT #114: nam='enqueue' ela= 102 p1=1431044098 p2=1073807913 p3=0 WAIT #114: nam='enqueue' ela= 103 p1=1431044098 p2=1073807914 p3=0 WAIT #114: nam='enqueue' ela= 102 p1=1431044098 p2=1073807915 p3=0 So if I check out p1 I see a UL lock mode 2: SQL run 1 SELECT chr(bitand(1431044098,-16777216)/16777215)|| 2 chr(bitand(1431044098, 16711680)/65535) Lock, 3 to_char( bitand(1431044098, 65535) )Mode 4* from dual Lo M -- - UL 2 cut **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
RE: Enqueue Waits in Oracle Financials
John, Yes, the PMON method is lock. The ICM runs 1 process with a sleep time of 30 seconds and a null value for cache size.All of our managers sleep at least 30 seconds, with the exception of a Service Manager, which is null. I'll probably end up logging a TAR. Thanks, Jay [EMAIL PROTECTED] 12/06/02 07:13PM Jay, Is the PMON method set to LOCK? See the output of the following SQL select profile_option_value from applsys.Fnd_Profile_Option_Values where Level_ID = 10001 And Level_Value = 0 And Application_ID = 0 And Profile_Option_ID = ( Select Profile_Option_Id From apps.Fnd_Profile_Options Where Profile_Option_Name = 'CONC_PMON_METHOD') I believe the ICM (Internal Concurrent Manager) places its own locks for scheduling reasons - maybe that is why you are seeing UL locks. You might also want to check with the Apps SYSADMIN account holder if anything has been changed wrt scheduling (could be the Cache size or Sleep seconds for any of the managers). You can verify if something has been changed by looking at the LAST_UPDATE_DATE on most FND tables. Hth, John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Enqueue Waits in Oracle Financials
Here is the output from the query: Sid/Ser# DB/OSUser StartTime Mins Rbs NameBlks Status -- --- -- -- - 14/106 APPS/applmgr12/06/02 01:29:32 518 RBS2 1 ACTIVE 33/537 APPS/applmgr12/06/02 07:59:42 128 RBS11 1 ACTIVE 160/285APPS/applmgr12/06/02 09:29:0139 RBS10 1 ACTIVE 165/234APPS/applmgr12/06/02 09:58:54 9 RBS14 1 ACTIVE 71/240 APPS/applmgr12/06/02 10:07:24 0 RBS24 1 ACTIVE 45/2 APPS/applmgr12/06/02 10:07:37 0 RBS8 1 ACTIVE 83/280 APPS/applmgr12/06/02 10:07:42 0 RBS27 1 ACTIVE 7 rows selected. The offending SID today is 16, which I don't see in the output from the above query. SQL run 1 select sid, 2 event, 3 total_waits tws, 4 total_timeouts tt, 5 time_waited tw, 6 average_wait avgw 7 from v$session_event 8 where event = 'enqueue' 9* order by time_waited desc,event Sess Total Total Time (ms) Avg (ms) ID Wait Event Waits TimoutsWaited Wait - - --- - 16 enqueue 55945589572543 102 10 enqueue 1 013 13 45 enqueue 1 012 12 I map this SID (16) back to the Internal Manager. By the way, SID 14 (with the highest Mins in your query) is the Service Manager. We scaled back our Alert manager to 1 process because we replaced some of our Alerts with triggers. The Alerts where just too much of a performance problem on our system (they were over 1/2 of our concurrent requests). Do we have any new fangled modules? Yes. Service and Contracts. Are we using OAM? It is installed, but we're not using it. I stumbled into it already and brought up some pretty graphs. Thanks, Jay [EMAIL PROTECTED] 12/05/02 07:39PM Jay, Does this come from the alert manager? Do you have any of those new-fangled 11i modules (or should I call the 'mangled'!!). You could use the script below (adapted from Govind who posted this a few days back) set pages 100 column sid_serial format a10 heading Sid/Ser# column username format a15 heading DB/OSUser column start_time format a18 heading StartTime column mins_pending format 999 heading Mins column used_ublk format heading Blks column name format a10 heading Rbs Name column status format a12 heading Status select sid || '/' || serial# sid_serial, username || '/' || osuser username, substr(t.start_time,1,18) start_time, round( ( sysdate - TO_DATE( start_time, 'MM/DD/YY HH24:MI:SS') ) *24*60 ,0 ) mins_pending, r.name, t.used_ublk , decode(t.space, 'YES', 'SPACE TX', decode(t.recursive, 'YES', 'RECURSIVE TX', decode(t.noundo, 'YES', 'NO UNDO TX', t.status))) status from v$transaction t, v$rollname r, v$session s where t.xidusn = r.usn and t.ses_addr = s.saddr order by t.start_time / If the OS user turns out to be 'applmgr' for any waiting TXN then pursue this from the CM side. Otherwise, you can look at the Forms users. In any case, are you using OAM (Oracle Applications Manager)? John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Enqueue Waits in Oracle Financials
Jay, Is the PMON method set to LOCK? See the output of the following SQL select profile_option_value from applsys.Fnd_Profile_Option_Values where Level_ID = 10001 And Level_Value = 0 And Application_ID = 0 And Profile_Option_ID = ( Select Profile_Option_Id From apps.Fnd_Profile_Options Where Profile_Option_Name = 'CONC_PMON_METHOD') I believe the ICM (Internal Concurrent Manager) places its own locks for scheduling reasons - maybe that is why you are seeing UL locks. You might also want to check with the Apps SYSADMIN account holder if anything has been changed wrt scheduling (could be the Cache size or Sleep seconds for any of the managers). You can verify if something has been changed by looking at the LAST_UPDATE_DATE on most FND tables. Hth, John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Jay Hostetter [mailto:[EMAIL PROTECTED]] Sent: Friday, December 06, 2002 8:51 AM To: Multiple recipients of list ORACLE-L Subject: RE: Enqueue Waits in Oracle Financials Here is the output from the query: Sid/Ser# DB/OSUser StartTime Mins Rbs Name Blks Status -- --- -- -- - 14/106 APPS/applmgr12/06/02 01:29:32 518 RBS2 1 ACTIVE 33/537 APPS/applmgr12/06/02 07:59:42 128 RBS11 1 ACTIVE 160/285APPS/applmgr12/06/02 09:29:0139 RBS10 1 ACTIVE 165/234APPS/applmgr12/06/02 09:58:54 9 RBS14 1 ACTIVE 71/240 APPS/applmgr12/06/02 10:07:24 0 RBS24 1 ACTIVE 45/2 APPS/applmgr12/06/02 10:07:37 0 RBS8 1 ACTIVE 83/280 APPS/applmgr12/06/02 10:07:42 0 RBS27 1 ACTIVE 7 rows selected. The offending SID today is 16, which I don't see in the output from the above query. SQL run 1 select sid, 2 event, 3 total_waits tws, 4 total_timeouts tt, 5 time_waited tw, 6 average_wait avgw 7 from v$session_event 8 where event = 'enqueue' 9* order by time_waited desc,event Sess Total Total Time (ms) Avg (ms) ID Wait Event Waits Timouts Waited Wait - - --- - 16 enqueue 55945589 572543 102 10 enqueue 1 0 13 13 45 enqueue 1 0 12 12 I map this SID (16) back to the Internal Manager. By the way, SID 14 (with the highest Mins in your query) is the Service Manager. We scaled back our Alert manager to 1 process because we replaced some of our Alerts with triggers. The Alerts where just too much of a performance problem on our system (they were over 1/2 of our concurrent requests). Do we have any new fangled modules? Yes. Service and Contracts. Are we using OAM? It is installed, but we're not using it. I stumbled into it already and brought up some pretty graphs. Thanks, Jay [EMAIL PROTECTED] 12/05/02 07:39PM Jay, Does this come from the alert manager? Do you have any of those new-fangled 11i modules (or should I call the 'mangled'!!). You could use the script below (adapted from Govind who posted this a few days back) set pages 100 column sid_serial format a10 heading Sid/Ser# column username format a15 heading DB/OSUser column start_time format a18 heading StartTime column mins_pending format 999 heading Mins column used_ublk format heading Blks column name format a10 heading Rbs Name column status format a12 heading Status select sid || '/' || serial# sid_serial, username || '/' || osuser username, substr(t.start_time,1,18) start_time, round( ( sysdate - TO_DATE( start_time, 'MM/DD/YY HH24:MI:SS') ) *24*60 ,0 ) mins_pending, r.name, t.used_ublk , decode(t.space, 'YES', 'SPACE TX', decode(t.recursive, 'YES', 'RECURSIVE TX', decode(t.noundo, 'YES', 'NO UNDO TX', t.status))) status from v$transaction t, v$rollname r, v$session s where t.xidusn = r.usn and t.ses_addr = s.saddr order by t.start_time / If the OS user turns out to be 'applmgr' for any waiting TXN then pursue this from the CM side. Otherwise, you can look at the Forms users. In any case, are you using OAM (Oracle Applications Manager)? John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music
RE: Enqueue Waits in Oracle Financials
Jay, You may want to refer to Metalink Doc Id #29787.1 and 34566.1. Those will explain what p2, p3 are in an enqueue wait. Unfortunately, those will not discuss p2, p3 for UL :( I know nothing about Oracle Apps (11i). May be John K. could comment on that. - Kirti -Original Message- Sent: Thursday, December 05, 2002 10:25 AM To: Multiple recipients of list ORACLE-L I noticed a lot of enqueue wait events in our 11i database. I ran some queries and was able to determine the process that is incurring these waits. I dutifully did a set event 10046 and examined the trace file. I've also queried v$lock. I've figured out that this is a UL (user defined) wait. Now I'm stuck. I haven't figured out exactly what we are waiting for. Although by monitoring the current SQL statement for the offending process, I see that it does a SELECT FOR UPDATE in the FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROGRAMS tables. This creates a TM lock, which I see, but I don't think it explains the UL lock. I've seen examples on how to interpret p1 for an enqueue lock, but not p2. I would appreciate a little guidance. I believe that the offending process is the Internal manager, but I would like to understand a little more about what is occurring. Is this a typical problem in 11i? I guess the ICM may issue user defined locks, then just waits for a certain! ! ! amount of time. I would guess that all 11i databases have a high number of enqueue waits if this is the case. I am running 11.5.6 against 8.1.7 on Tru64. Thank you, Jay Sample output from the trace: WAIT #114: nam='enqueue' ela= 102 p1=1431044098 p2=1073807913 p3=0 WAIT #114: nam='enqueue' ela= 103 p1=1431044098 p2=1073807914 p3=0 WAIT #114: nam='enqueue' ela= 102 p1=1431044098 p2=1073807915 p3=0 So if I check out p1 I see a UL lock mode 2: SQL run 1 SELECT chr(bitand(1431044098,-16777216)/16777215)|| 2 chr(bitand(1431044098, 16711680)/65535) Lock, 3 to_char( bitand(1431044098, 65535) )Mode 4* from dual Lo M -- - UL 2 Sample output from v$lock for SID 14 (not at the exact same time as the lock shown above): ADDR KADDR Sid TYID1ID2 LMODEREQUEST CTIME BLOCK - -- -- -- -- -- -- -- 00040147E578 00040147E5A014 TM 130213 0 2 0 78 0 000400B1B430 000400B1B45014 UL 1073741851 0 6 0 33188 0 000400B16340 000400B1636014 UL 1073807990 0 6 0 33158 0 I can see that there are quite a few UL waits: SQL run 1 SELECT ksqsttyp Lock, 2 ksqstget Gets, 3 ksqstwat Waits 4* FROM X$KSQST where KSQSTWAT 0 Lo Gets Waits -- -- -- TX 170144 59 UL 7275 6011 Other info: SQL SELECT * FROM v$sysstat WHERE cla 23 ss=4; STATISTIC# NAME CLASS VALUE -- -- -- 22 enqueue timeouts 4 6729 23 enqueue waits 4 6297 24 enqueue deadlocks 4 1 25 enqueue requests 4 852617 26 enqueue conversions 4 27889 27 enqueue releases 4 845696 SQL run 1 SELECT * 2FROM v$system_event 3* WHERE event = 'enqueue' EVENTTOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT --- -- --- enqueue 6881 6520 732348 106.430461 From a long query that joins v$process, v$session, v$session_event, fnd_concurrent_processes, fnd_concurrent_queues_vl and looks for enqueue wait events: DB_PROCESS Sid MANAGER_OS P USER_CONCURRENT_QUEUE_NAME EVENT TWS TT TW AVGW -- - -- - -- --- -- -- -- -- 1175422 92 1098457A Workflow Manager (DE) enqueue 1 0 1 1 1122160 13 1120706A PO Document Approval Manager enqueue 6 4 1341 223.5 1121613 25 1121812A INV Remote Procedure Manager enqueue 6 4 1424 237.33 1119743 24 1122331A INV Remote Procedure
RE: Enqueue Waits in Oracle Financials
Jay, Does this come from the alert manager? Do you have any of those new-fangled 11i modules (or should I call the 'mangled'!!). You could use the script below (adapted from Govind who posted this a few days back) set pages 100 column sid_serial format a10 heading Sid/Ser# column username format a15 heading DB/OSUser column start_time format a18 heading StartTime column mins_pending format 999 heading Mins column used_ublk format heading Blks column name format a10 heading Rbs Name column status format a12 heading Status select sid || '/' || serial# sid_serial, username || '/' || osuser username, substr(t.start_time,1,18) start_time, round( ( sysdate - TO_DATE( start_time, 'MM/DD/YY HH24:MI:SS') ) *24*60 ,0 ) mins_pending, r.name, t.used_ublk , decode(t.space, 'YES', 'SPACE TX', decode(t.recursive, 'YES', 'RECURSIVE TX', decode(t.noundo, 'YES', 'NO UNDO TX', t.status))) status from v$transaction t, v$rollname r, v$session s where t.xidusn = r.usn and t.ses_addr = s.saddr order by t.start_time / If the OS user turns out to be 'applmgr' for any waiting TXN then pursue this from the CM side. Otherwise, you can look at the Forms users. In any case, are you using OAM (Oracle Applications Manager)? John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Jay Hostetter [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 8:25 AM To: Multiple recipients of list ORACLE-L Subject: Enqueue Waits in Oracle Financials I noticed a lot of enqueue wait events in our 11i database. I ran some queries and was able to determine the process that is incurring these waits. I dutifully did a set event 10046 and examined the trace file. I've also queried v$lock. I've figured out that this is a UL (user defined) wait. Now I'm stuck. I haven't figured out exactly what we are waiting for. Although by monitoring the current SQL statement for the offending process, I see that it does a SELECT FOR UPDATE in the FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROGRAMS tables. This creates a TM lock, which I see, but I don't think it explains the UL lock. I've seen examples on how to interpret p1 for an enqueue lock, but not p2. I would appreciate a little guidance. I believe that the offending process is the Internal manager, but I would like to understand a little more about what is occurring. Is this a typical problem in 11i? I guess the ICM may issue user defined locks, then just waits for a certa! in! amount of time. I would guess that all 11i databases have a high number of enqueue waits if this is the case. I am running 11.5.6 against 8.1.7 on Tru64. Thank you, Jay Sample output from the trace: WAIT #114: nam='enqueue' ela= 102 p1=1431044098 p2=1073807913 p3=0 WAIT #114: nam='enqueue' ela= 103 p1=1431044098 p2=1073807914 p3=0 WAIT #114: nam='enqueue' ela= 102 p1=1431044098 p2=1073807915 p3=0 So if I check out p1 I see a UL lock mode 2: SQL run 1 SELECT chr(bitand(1431044098,-16777216)/16777215)|| 2 chr(bitand(1431044098, 16711680)/65535) Lock, 3 to_char( bitand(1431044098, 65535) )Mode 4* from dual Lo M -- - UL 2 Sample output from v$lock for SID 14 (not at the exact same time as the lock shown above): ADDR KADDR Sid TYID1 ID2 LMODEREQUEST CTIME BLOCK - -- -- -- -- -- -- -- 00040147E578 00040147E5A014 TM 130213 0 2 0 78 0 000400B1B430 000400B1B45014 UL 1073741851 0 6 0 33188 0 000400B16340 000400B1636014 UL 1073807990 0 6 0 33158 0 I can see that there are quite a few UL waits: SQL run 1 SELECT ksqsttyp Lock, 2 ksqstget Gets, 3 ksqstwat Waits 4* FROM X$KSQST where KSQSTWAT 0 Lo Gets Waits -- -- -- TX 170144 59 UL 7275 6011 Other info: SQL SELECT * FROM v$sysstat WHERE cla 23 ss=4; STATISTIC# NAME CLASS VALUE -- --- - -- -- 22 enqueue timeouts 4 6729 23 enqueue waits 4 6297 24 enqueue deadlocks 4 1 25 enqueue requests
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
RE: enqueue waits
see p2text and p3text for more. Sounds like a one-time long-held table/row lock to me. If you can, bounce the instance and recheck for reoccurence hth Ross I wish I could do statistics Mohan -Original Message- Sent: Tuesday, May 15, 2001 2:50 PM 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 -- - - 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. 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) 1T[ 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: Mohan, Ross 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).
RE: enqueue waits
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 -- - - 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. 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) 1T[ 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
RE: Enqueue waits
Thanks for the answer Steve. --- Steve Adams [EMAIL PROTECTED] escribi: Hi Pablo, The TM and TX waits are probably normal application tuning issues. You'll find some tips about reducing ST enqueue waits on the Ixora web site, MetaLink and elsewhere. The CU and SQ waits are relatively few and probably relatively brief, nevertheless there can be performance issues with these but I would focus on the others first. In general, the Anjo Kolk paper on "Oracle7 Wait Events and Enqueues" is the best starting point for information about different enqueue types. You can find it at http://www.evdbt.com/event.pdf @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- From: Pablo ksksksk [mailto:[EMAIL PROTECTED]] Sent: Thursday, 5 April 2001 0:26 To: Multiple recipients of list ORACLE-L Subject: Enqueue waits Hi List. Enqueue waits is on the TOP of my wait list. Event time_waited average_wait - --- enqueue21685596396.93 Here are the details: TY GETS WAITS -- - - CF 104 0 CI 10788 0 CU 25388 7 DL 259 0 DR 149 0 DX 56457 0 IS 180 0 MR 130 0 RT 1 0 SQ 2660 3 ST 10078 221 TY GETS WAITS -- - - TM398703 5 TS 13669 0 TX288025 116 UL 4923 0 US 43960 0 WL16 0 This is a Oracle Financials application I'm tuning. Can anybody explain what these types of ENQUEUE mean? And how can I avoid them. TIA ___ Do You Yahoo!? Enva mensajes instantneos y recibe alertas de correo con Yahoo! Messenger - http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= 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). ___ Do You Yahoo!? Enva mensajes instantneos y recibe alertas de correo con Yahoo! Messenger - http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= 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).
RE: Enqueue waits
Hi Pablo, The TM and TX waits are probably normal application tuning issues. You'll find some tips about reducing ST enqueue waits on the Ixora web site, MetaLink and elsewhere. The CU and SQ waits are relatively few and probably relatively brief, nevertheless there can be performance issues with these but I would focus on the others first. In general, the Anjo Kolk paper on "Oracle7 Wait Events and Enqueues" is the best starting point for information about different enqueue types. You can find it at http://www.evdbt.com/event.pdf @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 5 April 2001 0:26 To: Multiple recipients of list ORACLE-L Hi List. Enqueue waits is on the TOP of my wait list. Event time_waited average_wait - --- enqueue21685596396.93 Here are the details: TY GETS WAITS -- - - CF 104 0 CI 10788 0 CU 25388 7 DL 259 0 DR 149 0 DX 56457 0 IS 180 0 MR 130 0 RT 1 0 SQ 2660 3 ST 10078 221 TY GETS WAITS -- - - TM398703 5 TS 13669 0 TX288025 116 UL 4923 0 US 43960 0 WL16 0 This is a Oracle Financials application I'm tuning. Can anybody explain what these types of ENQUEUE mean? And how can I avoid them. TIA ___ Do You Yahoo!? Enva mensajes instantneos y recibe alertas de correo con Yahoo! Messenger - http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= 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: Steve Adams 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).
Re: enqueue waits from statspack report
CI - Cross-instance Call Invocatiom UL - User-defined locks [EMAIL PROTECTED] wrote: The following is from a Statspack report from an 8.0.5 database on a Sun server during a load test. Can anyone explain what type of enqueues these are and where I can find some doco on them? I did look on MetaLink and Steve Adams' site. Thanks. Enqueue activity for DB EnqueueGets Waits -- -- CI 12,144168 UL1,785 41 John Fedock iXL, Inc. mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.ixl.com http://www.ixl.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct 858-831-2229 San Diego, CA 92131 HOME DEPOT - The Big Boy's Toy store! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler 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).
