8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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

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.

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.