Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: many updates vs. one update
A copy of this was sent to "Drazen Kozic" <dkozic_at_eunet.yu> (if that email address didn't require changing) On Mon, 4 Jun 2001 17:22:01 +0200, you wrote:
>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.
>
do you have a tkprof report showing how the SQL is performing in the stored procedure -- if you could do that for just a dozen or so, something might pop out. If you don't know about tkprof see http://asktom.oracle.com/~tkyte/tkprof.html, it'll bounce you to the documentation.
It'll pretty much always be true that if you can do it in a single SQL statement it'll be faster then procedurally doing it.
>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
>
>
>
>
-- Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/ Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Mon Jun 04 2001 - 13:43:34 CDT