Re: Concurrent updates / low performance

try to increase your INITTRANS to the no. of your concurrent processes. Increase PCTFREE will help.

leonardo_ladeira_at_yahoo.com (Leonardo Ladeira) wrote in message news:<698f7e0a.0209280705.7e2c6cd5_at_posting.google.com>...
> 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 Thu Oct 03 2002 - 22:29:46 CEST