DBA Hub

📋Steps in this guide1/4

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

This article does a side-by-side speed comparison of PL/SQL and the Oracle JVM for some basic mathematical operations.

oracle miscconfigurationintermediate
by OracleDba
14 views
1

Overview

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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
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;
  } 

};
/

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;
/
2

Section 2

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. The following output clearly shows the Oracle JVM is significanly slower at performing mathematical operations than PL/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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
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;
/

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>

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;
      }
    }
  } 

};
/
3

Section 3

The package header now only needs a single Java call specification. 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. The tests now show the Oracle JVM is out-performing the PL/SQL for this basic looping and mathematical processing.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
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;
/

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;
/

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>
4

Section 4

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. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!