Improving performance of calculations in PL/SQL

From: Charlie Edwards <charlie3101_at_hotmail.com>
Date: 26 Jan 2004 02:21:56 -0800
Message-ID: <217ac5a8.0401260221.6b9f1d60@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 Received on Mon Jan 26 2004 - 04:21:56 CST