many updates vs. one update

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 Received on Mon Jun 04 2001 - 10:22:01 CDT