Re: Improving performance of calculations in PL/SQL

"Charlie Edwards" <charlie3101_at_hotmail.com> 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?

Charlie,

I suspect the difference in performance for the PL/SQL is down to the difference in CPU speed between your Intel based PC and the PA RISC (?) based HP server.

Are you sure that the PL/SQL calculations are the bottleneck in your batch run ? From the performance you are getting on your development machine you should be able to get a couple of billion loops of the above code in 100 hours !

You didn't provide any details about how you get the data into the PL/SQL procedure or what you do with the output, but I'll make a wild guess and say that these are probably the areas taking most of the time in the batch run.

Paul Dixon Received on Mon Jan 26 2004 - 06:38:46 CST