RE: Enqueue timeouts
Hi Iain, Let me quote from my book ... "Note also that the 'enqueue timeouts' statistic in V$SYSSTAT does not represent the number of enqueue wait timeouts. Rather, this statistic is incremented when an enqueue request or enqueue conversion is aborted entirely. This can be due to a distributed transaction timeout, but usually relates to locks requested in no-wait mode. ... It is sometimes suggested that 'enqueue_resources' should be increased to combat enqueue waits. But please note that there is absolutely no substance to this suggestion. Oracle will return an ORA-52 or ORA-53 error if it fails to find a free slot in the enqueue resources or enqueue locks fixed arrays respectively. Beyond that, the setting of the 'enqueue_resources' and '_enqueue_locks' parameters is unimportant." If you are really interested in setting these parameters, use V$RESOURCE_LIMIT. However, a more helpful course of action is to look at X$KSQST to investigate what type of enqueues those waits are coming from. There is a script called "enqueue_stats.sql" at http://www.ixora.com.au/scripts/locks.htm on the Ixora web site that might help. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 20 April 2001 21:40 To: Multiple recipients of list ORACLE-L Can anyone offer advice on what the problem may be with the following from v$sysstat on one of our databases. NAME VALUE -- - enqueue timeouts 17499101 enqueue waits446362 enqueue deadlocks 2 enqueue requests 47731729 enqueue conversions 1228500 enqueue releases 30232445 Enqueue_resources is set at 5000, DML_Locks is at 4000 and sessions at 412 so enqueue_resources looks high already but I came across a note which suggested increasing enqueue_resources where timeouts were occurring. Would anything else be causing this or is there a way to calculate what enqueue_resource should be set at. -- 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 timeouts
FOR YOUR INFORMATION ESIS and EPFAL are now part of Logica. The Internet email addresses of the staff has changed to the following - [EMAIL PROTECTED] eg [EMAIL PROTECTED] Emails using the old format will continue to be delivered until 30th June 2001. Steve Adams discusses enqueue_resources in some detail in his Oracle 8I internal services book. A brief subset of what he says is as follows There is no substance to increasing enqueue_resources to combat enqueue_waits. Oracle returns a ORA-52 or ORA-53 error if it fails to find a free slot in the enqueue resources or locks fixed arrays. If you are not seeing that error message then there is no need to increase enqueue_resources. Look at v$resource_limits and adjust enqueue_resources and enqueue_locks parameters to be > 20% above max utilisation recorded. Look at the dynamic table X$KSQST to find the type of resource that is being waited for Finally the enqueue_timeout statistic is misleading as it does not show timeouts but rather the number of enqueue requests that have been aborted. This may be because of timeouts but it may also be because a lock is held on the rows and the code specifies no wait therefore the call is aborted. I hope I have summarised what Steve says correctly and it helps Regards John -Original Message- From: Nicoll, Iain (Calanais) [mailto:[EMAIL PROTECTED]] Sent: 20 April 2001 12:40 To: Multiple recipients of list ORACLE-L Subject:Enqueue timeouts Can anyone offer advice on what the problem may be with the following from v$sysstat on one of our databases. NAME VALUE -- - enqueue timeouts 17499101 enqueue waits446362 enqueue deadlocks 2 enqueue requests 47731729 enqueue conversions 1228500 enqueue releases 30232445 Enqueue_resources is set at 5000, DML_Locks is at 4000 and sessions at 412 so enqueue_resources looks high already but I came across a note which suggested increasing enqueue_resources where timeouts were occurring. Would anything else be causing this or is there a way to calculate what enqueue_resource should be set at. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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). This e-mail and any files transmitted with it, are confidential to Logica and are intended solely for the use of the individual or entity to whom they are addressed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hallas, John 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).
