Concurrent updates / low performance

From: Leonardo Ladeira <leonardo_ladeira_at_yahoo.com>
Date: 26 Sep 2002 20:25:40 -0700
Message-ID: <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. Received on Fri Sep 27 2002 - 05:25:40 CEST

Original text of this message