Re: Concurrent updates / low performance

jocave_at_yahoo.com (Justin Cave) wrote in message news:<233b7a65.0209271601.124ef31b_at_posting.google.com>...
> 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!
>
> A dumb question, which may serve only to highlight my ignorance...
> How many processors in your box?
>
> I'm assuming your query is primarily bound on one device (either disk
> or CPU). If there's only 1 CPU, 15 threads, each of which require
> 0.8s of CPU (or 0.8s of disk access) would take exactly 12s. If
> there's only 1 processor in the box, your total time won't go down
> significantly.
>
> Justin Cave

Hi Justin,

Actually the whole process takes about 28 seconds! If we consider for some reason that all the updates are serialized, we could take 0.8 x 15 = 12 seconds! However what is happening is that one single update can take 12 seconds if there are 15 threads running!!! So, what it seems is that using multithreads in this case is useless!

I have only 1 CPU and 1 disk (development environment). What I expected is that Oracle could make at least some part of work paralelly. So, for instance, while waiting for data from the disk, another thread could be searching its B-Tree structures to lock the other rows for update. So, the whole process could take less than 0.8 seconds per update and not 1.8s per update, as it is happening (28 seconds / 15).

Thank you very much for your answer!

Leonardo. Received on Sat Sep 28 2002 - 17:05:08 CEST