Concurrent updates / low performance
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:
- 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?
- 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)?
- 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