Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: many updates vs. one update

Re: many updates vs. one update

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 04 Jun 2001 14:43:34 -0400
Message-ID: <3rinhtk40sq3o3r53hknalpqqkiphdvi99@4ax.com>

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 Corp 
Received on Mon Jun 04 2001 - 13:43:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US