Main goal of this article is to show performance comparison mainly between PL/SQL and Java (running inside and outside of the Oracle Db), with included C and Python code just to be able to compare results.

This may hopefully help you to decide where you are going to put Java CPU intensive code when you’ll have such case.

The following text is just extract from my presentation held last year on regional OUG.

I’ve used the following Java Code for measuring performance:


public class TestSpeed {

   public static void main(String args[]){
   	  float x;
      for(int i = 1; i <= 10000000; i++ ){
      	x = i / 1000;
      }
   }
}

There are many methods to load Java code into the Db.

Two most common approaches are:
-loadjava utility
-DDL SQL command

You’ll need to create wrapper for Java Stored Procedure method:


create or replace procedure testspeed 
as 
language java name 'TestSpeed.main(java.lang.String[])';

Finally you’ll be able to execute it:

SCOTT@test> exec testspeed;
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.87

After JIT (you have to at least 2 time execute the same code) we have much better performance when xecuting the same code:

SCOTT@test> exec testspeed;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04

If I’m using native compile (ahead of time compilation), the result are same as with JIT with one important difference: by using native compilation, I’m able to control which methods I want to have in compiled state, which means I don’t have to rely on JIT.

SCOTT@test> exec testspeed2;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03

For testing purpose I’ve created the same method in C.


#include 
void main(void)
{
double x;
for (int i = 0; i < 10000000; i++)
{
	x = i / 1000;
}
}

oracle@xxx-xxx-xx-1p:/tmp > time speedtest
real 0m0.076s
user 0m0.043s
sys 0m0.001s

As you can see, Java in native compiled state (or after the JIT) is running at the same speed as C code.

Next step is to see how PL/SQL store procedure will execute the same code.


CREATE OR REPLACE PROCEDURE test_speed AS
v_number NUMBER;
begin

	FOR i IN 1 .. 10000000 LOOP
		v_number := i / 1000;
	end loop;
end;

PL/SQL code – interpreted

exec test_speed;
1.768 sec

PL/SQL code – native compiled level 3

exec test_speed;
elapsed: 00:00:01.73

As you can see, the results are disappointing. Although we’ve used native compilation, we expected to see much better results, as this is pure CPU intensive code. For interpreted version of PL/SQL code, results are within my expectation.

If I rewrite the PL/SQL code to use new data types (introduced with 11g version of Db), I finally get much better results:


create or replace procedure test_speed8 as
	v_number simple_double :=0;
	x simple_double := 1000;
	y simple_double := 0;

begin
	for i in 1 .. 10000000 loop	
		y := i;
		v_number := y / x;
	end loop; 
end;

exec test_speed8;
PL/SQL procedure successfully completed.
Elapsed: from 00:00:00.65 up to: 00:00:00.81 sec

The same results I’ve got with native compilation.

Now let’s see Java client side code (Java running out of Oracle Db):

time java TestSpeed

real 0m0.102s
user 0m0.090s
sys 0m0.019s

Results are better then PL/SQL optimized code, but not as fast as Java in Db or C code.

Finally, let’s see results for Python:


#!/usr/bin/python
# Python for loop using range()

for i in range(1,10000000):
	x = i / 1000.0	

time python testspreed.py
real 0m1.882s
user 0m1.756s
sys 0m0.128s

time python3 testspreed.py
real 0m2.226s
user 0m2.222s
sys 0m0.005s

In first case I’m using Python 2x, and Python 3x in second case.
As expected, Python was the slowest of all combination, although version 2 is faster then version 3.

Let’s build table that will consist of all tested combination.

plsql_vs_java_vs_c_vs_python_cpu_intensive

Summary:

1.
There are different kind of performance tests that can be performed, but I concentrate on most simplistic case: just an ordinary loop statement.

2.
This tests cover only CPU intensive tasks. Results for data intensive tests will be covered in other blog. For now, I can only tell that results will be very different from CPU intensive tasks.

3.
In case of Python, we can speed it up by using Python numerical libraries, but that is not a goal of this tests.

4.
I rank the test values by joining test values into the same group where results difference are below 0.1 sec (for example, the first four places are within the same group, as well as group with rank 4).

5.
If you have CPU intensive task, there are basically three options:

a) Create Java Stored Procedure that will run inside Oracle Db (in the same namespace).
In cases where the method that is working on CPU intensive task will be called many times, you can rely on JIT.
In all other cases it is much better to use Java Stored Procedure in native compiled state.

b) Process CPU intensive tasks on Middle Tier by using Application Server, or on the client machine.

c) Create external C procedure callable from Oracle Db.

As the last option c) is hard to configure and debug, there are only two options left.

Only two cases are suitable for doing CPU intensive calculations inside the Oracle DB:
-when you have enough resources on Db host (CPU, memory)
-when you have to move large amount of data through the network (that will be covered in one of the future posts)

As Db resources (CPU and memory) are often well used (nobody wants to spend extra money for Oracle Db licenses that are not going to be used), the most common scenario is to do all calculations on Application server (or grid of apps servers) or on the client machine.


Comments

Werner
2019-07-17 11:49:13
A straight forward test with very impressive results. Nothing to add, the numbers speak for themselves. It would be interesting to see the performance on filtered datasets with 1000/1000000/1000000000 rows - a simple Select From Where test. With Java, using a dataframe API, Oracle, using plain SQL on an indexed table and Python, using pd.DataFrame. I guess there are only a few people who would be able to really predict a result, although many with a biased opinion on it.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.