Re: Tuning a series of batch statements

As you don't issue specific lock commands, and you also don't issue a select for update before you actually you update, the lock on your table escalates to a table level lock. This is your current enqueue problem. With the current state of Oracle, using a table for a sequence mechanism is questionable. You should try to use sequences if you can.

Hth,

Sybrand Bakker, Oracle DBA

"Myron Wintonyk" <mwintony_at_med.ualberta.ca> wrote in message news:3A5D3BF9.62AE0709_at_med.ualberta.ca...
> I have a series of batchjobs that all access the same record in the same
> table. Each on increments
> the value of a counter. The statment looks something like this
>
> update THETABLE set COUNTER = :1 where ...
> commit;
>
> I have a total to 20 processes all issueing this statment. As a result,
> the application is in a constant
> enqueue wait. I'm looking for some suggestions on what I can do.
>
> Of specific help would be knowing exactly what has to happen after the
> update and commit before
> the next process can update that record. Here's what it might be:
>
> - Lock the block in memory.
> - Write the rollback information.
> - Update the block in memory.
> - Write a redo log entry.
> - Release the block in memory.
>
> Does anyone know the exact sequence? If so, that would point me in the
> right direction for tuning this
> application.
>
> Myron Wintonyk
> University of Queensland
> Brisbane Queensland
> Australia
>
>
Received on Wed Jan 10 2001 - 23:35:40 CST