PL/SQL Vs. Oracle JVM: Speed Comparison for Mathematical Operations

Advertisements

Oracle has been shipping a Java Virtual Machine (JVM) as part of the database since Oracle 8i. Several years ago I remember hearing someone mention the Oracle JVM was quciker for mathematical operations than PL/SQL, but never really paid it much attention. In this article I do a side-by-side speed comparison of PL/SQL and the Oracle JVM for some basic mathematical operations.

Note. I'm a PL/SQL programmer and approach this from that perspective. That is why the tests are all based around operations being called from PL/SQL.

First we need to create some Java in the database to perform our mathematical operations. The following Java code defines a class called "Mathematics", which contains five static functions to perform the five operations (+, -, *, /, Mod) we will compare.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Mathematics" AS
import java.lang.*;

public class Mathematics
{
  
  public static int addFn (int Number1, int Number2)
  {
    return Number1 + Number2;
  } 

  public static int subtractFn (int Number1, int Number2)
  {
    return Number1 - Number2;
  } 

  public static int multiplyFn (int Number1, int Number2)
  {
    return Number1 * Number2;
  } 

  public static int divideFn (int Number1, int Number2)
  {
    return Number1 / Number2;
  } 

  public static int modFn (int Number1, int Number2)
  {
    return Number1 % Number2;
  } 

};
/

Next we define a package header containing call specifications for the five Java functions defined previously, as well as a procedure to actually test them.

CREATE OR REPLACE PACKAGE maths_test AS

FUNCTION addFn (p_number1  IN  NUMBER, p_number2  IN  NUMBER) RETURN NUMBER
  AS LANGUAGE JAVA 
  NAME 'Mathematics.addFn (int, int) return int';

FUNCTION subtractFn (p_number1  IN  NUMBER, p_number2  IN  NUMBER) RETURN NUMBER
  AS LANGUAGE JAVA 
  NAME 'Mathematics.subtractFn (int, int) return int';

FUNCTION multiplyFn (p_number1  IN  NUMBER, p_number2  IN  NUMBER) RETURN NUMBER
  AS LANGUAGE JAVA 
  NAME 'Mathematics.multiplyFn (int, int) return int';

FUNCTION divideFn (p_number1  IN  NUMBER, p_number2  IN  NUMBER) RETURN NUMBER
  AS LANGUAGE JAVA 
  NAME 'Mathematics.divideFn (int, int) return int';

FUNCTION modFn (p_number1  IN  NUMBER, p_number2  IN  NUMBER) RETURN NUMBER
  AS LANGUAGE JAVA 
  NAME 'Mathematics.modFn (int, int) return int';

PROCEDURE test (p_operation IN VARCHAR2);

END maths_test;
/

The package body only contains the definition of the "test" procedure, which compares the speed of the PL/SQL and Java versions of the specified operation.

CREATE OR REPLACE PACKAGE BODY maths_test AS

PROCEDURE test (p_operation IN VARCHAR2) AS
  l_start SIMPLE_INTEGER := 0;  -- Use PLS_INTEGER prior to 11g
  l_val   SIMPLE_INTEGER := 0;  -- Use PLS_INTEGER prior to 11g
  l_loops NUMBER         := 1000000;
BEGIN
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    CASE LOWER(p_operation)
      WHEN '+'   THEN l_val := i + 2;
      WHEN '-'   THEN l_val := i - 2;
      WHEN '*'   THEN l_val := i * 2;
      WHEN '/'   THEN l_val := i / 2;
      WHEN 'mod' THEN l_val := MOD(i, 2);
    END CASE;
  END LOOP;

  DBMS_OUTPUT.put_line('PL/SQL (' || p_operation || '): ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    CASE LOWER(p_operation)
      WHEN '+'   THEN l_val := addFn(i, 2);
      WHEN '-'   THEN l_val := subtractFn(i, 2);
      WHEN '*'   THEN l_val := multiplyFn(i, 2);
      WHEN '/'   THEN l_val := divideFn(i, 2);
      WHEN 'mod' THEN l_val := modFn(i, 2);
    END CASE;
   END LOOP;

  DBMS_OUTPUT.put_line('Java (' || p_operation || '): ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END test;

END maths_test;
/

The following output clearly shows the Oracle JVM is significanly slower at performing mathematical operations than PL/SQL.

SQL> SET SERVEROUTPUT ON
SQL> EXEC maths_test.test('+');
PL/SQL (+): 9 hsecs
Java (+): 2260 hsecs

PL/SQL procedure successfully completed.

SQL> EXEC maths_test.test('-');
PL/SQL (-): 12 hsecs
Java (-): 2314 hsecs

PL/SQL procedure successfully completed.

SQL> EXEC maths_test.test('*');
PL/SQL (*): 16 hsecs
Java (*): 2332 hsecs

PL/SQL procedure successfully completed.

SQL> EXEC maths_test.test('/');
PL/SQL (/): 53 hsecs
Java (/): 2417 hsecs

PL/SQL procedure successfully completed.

SQL> EXEC maths_test.test('mod');
PL/SQL (mod): 70 hsecs
Java (mod): 2360 hsecs

PL/SQL procedure successfully completed.

SQL>

That test isn't exactly fair for the JVM because we are constanly flipping between PL/SQL and Java. What if we rewrite it so the whole of the Java test is processed in Java? The revised Java class would look like this.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Mathematics" AS
import java.lang.*;

public class Mathematics
{
  
  public static void testJava (int operation, int loops)
  {
    int val;
    for (int i=1; i <= loops; i++) {
      switch (operation) {
            case 1: val = i + 2; break;
            case 2: val = i - 2; break;
            case 3: val = i * 2; break;
            case 4: val = i / 2; break;
            case 5: val = i % 2; break;
      }
    }
  } 

};
/

The package header now only needs a single Java call specification.

CREATE OR REPLACE PACKAGE maths_test AS

PROCEDURE testjava (p_operation IN NUMBER, p_loops IN NUMBER)
  AS LANGUAGE JAVA 
  NAME 'Mathematics.testJava (int, int)';

PROCEDURE test (p_operation IN VARCHAR2);

END maths_test;
/

The package body has a couple of changes. First, it translates the operation parameter to a number so it can be passed to Java and used in a switch statement. It looks a little stupid, but it saves a little bit of messing around in the Java. Second, there is a single call to the java code passing the operation and the number of iterations it should process.

CREATE OR REPLACE PACKAGE BODY maths_test AS

PROCEDURE test (p_operation IN VARCHAR2) AS
  l_start     PLS_INTEGER := 0;
  l_val       PLS_INTEGER := 0;
  l_loops     NUMBER         := 1000000;
  l_operation NUMBER;
BEGIN
  -- Translate the original operaton string to number so I can use switch in Java.
  CASE LOWER(p_operation)
    WHEN '+'   THEN l_operation := 1;
    WHEN '-'   THEN l_operation := 2;
    WHEN '*'   THEN l_operation := 3;
    WHEN '/'   THEN l_operation := 4;
    WHEN 'mod' THEN l_operation := 5;
  END CASE;

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    CASE l_operation
      WHEN 1 THEN l_val := i + 2;
      WHEN 2 THEN l_val := i - 2;
      WHEN 3 THEN l_val := i * 2;
      WHEN 4 THEN l_val := i / 2;
      WHEN 5 THEN l_val := MOD(i, 2);
    END CASE;
  END LOOP;

  DBMS_OUTPUT.put_line('PL/SQL (' || p_operation || '): ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  testJava(l_operation, l_loops);

  DBMS_OUTPUT.put_line('Java (' || p_operation || '): ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END test;

END maths_test;
/

The tests now show the Oracle JVM is out-performing the PL/SQL for this basic looping and mathematical processing.

SQL> SET SERVEROUTPUT ON
SQL> EXEC maths_test.test('+');
PL/SQL (+): 7 hsecs
Java (+): 4 hsecs

PL/SQL procedure successfully completed.

SQL> EXEC maths_test.test('-');
PL/SQL (-): 10 hsecs
Java (-): 4 hsecs

PL/SQL procedure successfully completed.

SQL> EXEC maths_test.test('*');
PL/SQL (*): 15 hsecs
Java (*): 4 hsecs

PL/SQL procedure successfully completed.

SQL> EXEC maths_test.test('/');
PL/SQL (/): 40 hsecs
Java (/): 6 hsecs

PL/SQL procedure successfully completed.

SQL> EXEC maths_test.test('mod');
PL/SQL (mod): 62 hsecs
Java (mod): 5 hsecs

PL/SQL procedure successfully completed.

SQL>

These results are consistent for this test on database versions (9.2, 10.2, 11.1 and 11.2).

Does this mean that you should rewrite all your PL/SQL to Java? No. We've not taken into account database interaction, which is afterall what PL/SQL is for, and we've also not tested the scalability or impact of multiple users on the Oracle JVM. I've avoided the issue of native compilation, since both PL/SQL and Java can be natively compiled. I just thought it was interesting.