Re: many updates vs. one update

"Drazen Kozic" <dkozic_at_eunet.yu> wrote in message news:9fg9bv$3a2$1_at_news.eunet.yu...
> Hi ,
>
> My problem is following:
>
> We run Oracle 8.1.7 on IBM RS/6000 cluster (4 CPU each node) with AIX
 4.3.3.
> (Parallel Query, Parallel Server etc)
> Everything works fine but...
> Monthly our application makes bills for 100000 customers. That procedure
 is
> implemented as PL/SQL procedure. The procedure runs very slow,
 approximately
> 2 bills per second. The procedure works as follows (simplified):
> - make bill record (single insert)
> - select records from huge table, makes some sum, and from that makes bill
> items.
> - add taxes on the bill items
> - update records in the huge table to "sign" them
> - commit the transaction
> - proceed to next bill
>
> On this way our billing procedure finishes its work in about 50000 sec
 (13.9
> hours)
>
> The problem is in the update we do on that huge table. The table has 50
> millions rows. It is partitioned. Every partition has 8 millions of rows.
 We
> update rows in one partition. We have all necessary indexes (we think so).
> If we exclude update from our procedure it runs 10-12 times faster (1.5
> hours for all bills).
> We intensively use Oracle Parallel Query, but here it is unusable in this
> case because transactions are short.
> We tried to make one big update of all records after making all bills. It
> runs very fast (alter session enable parallel dml) and updates all 8
> millions rows in one transaction in 0.5 hours. If we compare times (13.9
 vs.
> 1.5+0.5) the difference is significant.
>
> Does anybody has suggestion how to improve performance in the first case.
> I will appreciate any help.
> Sorry for may pure English
> Drazen Kozic
>
> drazen.kozic_at_asweng.com
>
>
>
>
>

Without looking at the actual code, and explain results, or info about your database config (you just 'might' have improperly sized redologs) this is just a too broad question, to make any sensible recommendation.

If the update can be done in one statement, it should be done in one statement, or if you are running 8i (why do people *NEVER* specify which version they are running????) you could use the FORALL keyword to do a bulk update in pl/sql. If you execute individual updates you will have one context switch per update, and I simply don't want the stupidity of committing every single individual update on that table.

Hth anyway,

Sybrand Bakker, Oracle DBA Received on Mon Jun 04 2001 - 13:23:58 CDT