Re: Concurrent updates / low performance

postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0209270428.3218e2f3_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!
> >
> > 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.
>
>
Hi Bakker, thank you very much for your answer!

> 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.
>

I will check the redo logs and rollback segments as you mentioned. However, as I am using update with hostarrays, I will only commit after the update command has finished, so after the hole hostarray is updated, which for me will be 86 entries. Is this right?

> In a properly set up database locking is NOT an issue, and you should
> not 'disable' it (Not that you really can't)
Can you provide me a way to do that or point to a document? I could not find this option in any document I read. I would really appreciate it!

> 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.

I executed before the update command the following pro*C code:

EXEC SQL LOCK TABLE ZZZ IN ROW SHARED MODE; Unfortunately I still have the same problem! :(

>
> Hth
>
> Sybrand Bakker
> Senior Oracle DBA
>
>
> Regards
>
> Sybrand Bakker
> Senior Oracle DBA

Thank you,

Leonardo. Received on Sat Sep 28 2002 - 02:14:32 CEST