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.
Posts
0 comments:
Post a Comment