Re: enqueque_resources

The statement that enqueue timeouts should be zero is not really true. Oracle calculates how many enqueue resources to allocate. You can override this via the init.ora, but doing so will not result in zero waits on anything other than a very small system unless you are just plan short of enqueues which is most often not the case.

Enqueue waits can be caused by:
1) Conflict for temporary tablespace transaction lock -- Look at your sort tablespace free space fragmentation, initial allocation, and average load. Maybe you need more sort space or need to declare the tablespace temporary. Picking a larger initial allocation, setting the next to match with a pctincrease of zero may help.
2) No transaction work area available in a data block for an update transaction to use. -- Check the initrans allocations on your heavy update tables, i.e., what is the likelihood that more than one user will be updating rows in the same Oracle data block and if the table was created with the default of 1 initran area maybe it should be re-created with two or initrans
3) Two or more processes want the same row concurrently. -- This requires application re-design. Sometime sequences can be used to eliminate this type of problem

Adding more enqueues will not fix the problem if the above three item are the cause. I hope this helps.

Ed Lufker <elufker_at_swcp.com> wrote in article <
>
> I am running the following sql
> select 'Warning: Enqueue Timeouts are '||value||'. They should be zero if
> the INIT.ora parameter is ' line1,
> 'high enough. Try increasing INIT.ora parameter ENQUEUE_RESOURCES
> and see
> if the Timeouts reduces.'
> from v$sysstat
> where name = 'enqueue timeouts'
> and value > 0
> /
>
> It show me like 1145 waits, it should be zero. How high can I grow this
> parameter. I am on a sun e4000 running solaris 2.5.1 and oracle 7.3.3.0.
>
> thank in advance for any help
> eddie lufker
Received on Tue Jul 07 1998 - 09:06:59 CDT