Re: Concurrent updates / low performance

leonardo_ladeira_at_yahoo.com (Leonardo Ladeira) wrote in message news:<698f7e0a.0209261925.505371a_at_posting.google.com>...
> Hi,
>
> I hope anybody can help me on this issue....
>
> I use Oracle 8 running over AIX 4.3
>
> My application has only one table and a lot of concurrent updates (up
> to 15 per second). I made an embedded SQL function using host arrays
> to update a couple of rows (aprox 86) from the table.
>
> When I call this function once (1 single thread), it takes aprox 0.8s
> to execute.
>
> However, if I have 15 different threads calling this function at the
> same time, one single update can take up to 12 seconds!!! And all the
> 15 updates are related to DIFFERENT rows. The fastest update from the
> 15 takes at least 6 seconds!
>
> So, what I can't understand is:
>
> 1) Doesn't update command acquire a row-level locking by default? So
> why updates are influencing the execution time from each other, as
> they are not trying to modify same rows? Do I have to configure
> anything to make update acquire the row-level locking?
>
> 2) Considering that for any reason the update operation for a table is
> serialized, which means that one can take place only after the other
> has finished, why the first update (which is the fastest) takes longer
> (6 seconds) than when I tried a single update (0.8 s)?
>
> 3) Is there any way I can disable locking from oracle update
> operation? I could at application level guarantee data integrity.
>
>
> Please any advise is welcome!
>
> Thank you very much,
>
> Leonardo.

I think your problem is lousy database configuration - you don't have apppropiately sized redo logs, so you are switching all the time
- you don't have appropiately sized rollback segments and above all
- you are committing every individual record.

In a properly set up database locking is NOT an issue, and you should not 'disable' it (Not that you really can't) You also may want to check out whether you use the appropiate commands:
if you don't explicitly lock the table in row share mode, and/or issue select for update
before you update the record, you'll get a table level lock. Of course this is documented in the application developers manual.

Hth

Sybrand Bakker
Senior Oracle DBA

Regards

Sybrand Bakker
Senior Oracle DBA Received on Fri Sep 27 2002 - 14:28:01 CEST