Example 1. Thin wrapper for executing SQL statements.

BEGIN
   FOR department IN  (SELECT department_id d, department_name
                         FROM departments
                     ORDER BY department_name)
   LOOP
      DBMS_OUTPUT.PUT_LINE (   CHR (10)
                            || department.department_name);

      FOR employee IN  (SELECT last_name
                          FROM employees
                         WHERE department_id = department.d
                      ORDER BY last_name)
      LOOP
         DBMS_OUTPUT.PUT_LINE (   '- '
                               || employee.last_name);
      END LOOP;
   END LOOP;
END;

There are many other applications and programs, however, that rely on PL/SQL to perform computationally-intensive tasks that are all but completely independent of the database. It is, after all, a fully functional procedural language. Consider, for example, the code shown in Example 2. This program takes on the task of finding all right-angled triangles with all side lengths integer (a.k.a. perfect triangles). We must count only unique triangles-that is, those whose sides are not each the same integral multiple of the sides of a perfect triangle already found.

Example 2. Computing perfect triangles.

CREATE OR REPLACE PROCEDURE perfect_triangles (p_max IN INTEGER)
IS
   t1             INTEGER;
   t2             INTEGER;
   long           INTEGER;
   short          INTEGER;
   hyp            NUMBER;
   ihyp           INTEGER;

   TYPE side_r IS RECORD (
      short                         INTEGER,
      long                          INTEGER);

   TYPE sides_t IS TABLE OF side_r
      INDEX BY BINARY_INTEGER;

   unique_sides   sides_t;
   n      INTEGER := 0 /* curr max elements in unique_sides */;
   dup_sides      sides_t;
   m      INTEGER := 0 /* curr max elements in dup_sides */;

   PROCEDURE store_dup_sides (p_long IN INTEGER, p_short IN INTEGER)
   IS
      mult         INTEGER := 2;
      long_mult    INTEGER :=  p_long * 2;
      short_mult   INTEGER :=  p_short * 2;
   BEGIN
      WHILE (long_mult < p_max)
         OR (short_mult < p_max)
      LOOP
         n := n + 1;
         dup_sides (n).long := long_mult;
         dup_sides (n).short := short_mult;
         mult := mult + 1;
         long_mult := p_long * mult;
         short_mult := p_short * mult;
      END LOOP;
   END store_dup_sides;

   FUNCTION sides_are_unique (p_long IN INTEGER, p_short IN INTEGER)
      RETURN BOOLEAN
   IS
   BEGIN
      FOR j IN 1 .. n
      LOOP
         IF      (p_long = dup_sides (j).long)
             AND (p_short = dup_sides (j).short)
         THEN
            RETURN FALSE;
         END IF;
      END LOOP;

      RETURN TRUE;
   END sides_are_unique;
BEGIN                            /* Perfect_Triangles */
   t1 := DBMS_UTILITY.get_time;

   FOR long IN 1 .. p_max
   LOOP
      FOR short IN 1 .. long
      LOOP
         hyp := SQRT (  long * long + short * short);
         ihyp := FLOOR (hyp);

         IF   hyp
            - ihyp < 0.01
         THEN
            IF (ihyp * ihyp =
                        long * long
                      + short * short
               )
            THEN
               IF sides_are_unique (long, short)
               THEN
                  m := m + 1;
                  unique_sides (m).long := long;
                  unique_sides (m).short := short;
                  store_dup_sides (long, short);
               END IF;
            END IF;
         END IF;
      END LOOP;
   END LOOP;

   t2 := DBMS_UTILITY.get_time;
   DBMS_OUTPUT.put_line (
         CHR (10)
      || TO_CHAR (((  t2
                    - t1
                   ) / 100
                  ), '9999.9')
      || ' sec'
   );
END perfect_triangles;

This program implements an exhaustive search among candidate triangles with all possible combinations of lengths of the two shorter sides, each in the range of one to a specified maximum. Testing whether the square root of the sum of the squares of the two short sides is within 0.01 of an integer coarsely filters each candidate. Exactly applying Pythagoras's theorem using integer arithmetic tests triangles that pass this test. Candidate- perfect triangles are tested against the list of multiples of perfect triangles found so far. Each new unique perfect triangle is stored in a PL/SQL table, and its multiples (up to the maximum length) are stored in a separate PL/SQL table to facilitate uniqueness testing.

The implementation thus involves a doubly nested loop with these steps at its heart: several arithmetic operations, casts and comparisons; calls to procedures implementing comparisons driven by iteration through a PL/SQL table (with yet more arithmetic operations); and extension of PL/SQL tables where appropriate.

So what impact does native compilation have on such code? The elapsed time was measured for p_max =5000 (that is, 12.5 million repetitions of the heart of the loop) using interpreted and natively compiled versions of the procedure. The times were 548 seconds and 366 seconds, respectively (on a Sun Ultra60 with no load apart from the test). Thus, the natively compiled version was about 33 percent faster.

That's not bad for a semi-transparent enhancement. In other words, no code changes were required in our application. And, while for data-intensive programs, native compilation may give only a marginal performance improvement, we've never seen it give performance degradation. So how do you turn on native compilation? Read on...