Re: Improving performance of calculations in PL/SQL

charlie3101_at_hotmail.com (Charlie Edwards) wrote in message news:<217ac5a8.0401260221.6b9f1d60_at_posting.google.com>...
> Hello,
>
> I've been drafted in to a project to improve performance of a batch
> run. At the moment it's taking over 100 hours to run. I've done a
> load of obvious things but have only got about a 20-25% improvement in
> performance.
>
> The main guts of the application is a series of loops, but it's not
> doing anything particularly complicated. So I thought to myself, is
> there something wrong in the Oracle set-up.
>
> I ran the following script on our development machine to mimic the
> sort of calculations that need doing:
> ----------------------------------------------------------
> DECLARE
> TYPE typ1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
> a typ1;
> t NUMBER := DBMS_UTILITY.get_time();
> w NUMBER := 0;
> x NUMBER := 0.1;
> y NUMBER := 0;
> z NUMBER := 1;
> BEGIN
> -- Load some test data into an array
> FOR t IN 0 .. 1000000 LOOP
> a(t) := 1 + (1 / (9999 + MOD(t, 7678 )));
> END LOOP;
> t := DBMS_UTILITY.get_time();
> -- Loop round doing a few calculations
> FOR t IN 0 .. 1000000 LOOP
> w := GREATEST(w,a(t));
> x := x * a(t);
> y := y + a(t) + a(t);
> z := w + x + y - a(1000000 - t);
> END LOOP;
> DBMS_OUTPUT.put_line('loop finished in ' ||
> ABS((DBMS_UTILITY.get_time() - t) / 100));
> END;
> /
> ----------------------------------------------------------
> loop finished in 32.25
>
> That's on our HP-UX machine running 8.1.7.4
>
> Hmmm .. I wonder. I tried it on production and got much the same.
> Then I tried it on my PC (default install) and it came back with 11.27
> seconds.
>
> So my PC is nearly 3 times quicker than the Unix machine!
>
> Can anybody suggest where I might suggest the DBA may look to improve
> the performance?
>
> Thanks
>
> CE

CE, your PC does not have a concurrent user load to contend with so the difference in performance may not be as great as it appears.

First, if you code is computation heavy and SQL light then you would be better off performing it in Java or Pro*C rather than in pl/sql.

To improve the pl/sql itself if changing from pl/sql is undesired or not practical then:  

1- substitute pls_integer for any numbers of the form (n,0) as this will give you native machine binary operations rather than library math.

2- If you are using mod() to time commit loops repace mod with counters and IF tests. (Thomas Kyte has demostrated that this is faster than mod division)

3- Make sure you release any pl/sql tables as soon as you are done with them so that the memeory is available for reuse.

4- If you reuse any pl/sql tables make sure you "trucate" them rather than deleting each entry before reuse [just as with work heap (normal) tables].

5- review the loops to make sure any statement in them can not be moved up to the outer loop.

6- look for common statements on both paths of an IF statement and move it up

7- look for any large chunks of identical code that could be replaced with a procedure and a call to it

8- if you have captive procedures (procedures codes into a bigger pl/sql block and called only from that block) repace the procedure lists with global variables to reduce call overhead

HTH -- Mark D Powell -- Received on Mon Jan 26 2004 - 08:46:58 CST