In this article I’ll provide a very simple way of how to test Oracle CPU single-thread performance.
Apart from that, I’ll cover performance boost you might expect when upgrading your server (especially CPU), expected performance boost when performing simple upgrade of the Oracle database, and issues and workarounds related to native compilation of PL/SQL code.
The code is very simplistic, but it seems that the same code has been copied on many sites, It’s a kind of standard.
For DBA it is important to know what kind of a pure speed he/she can get, especially when underlying hardware has been upgraded.
Here is the code you can use to test your system:
create or replace procedure cpu_speed_interpreted
as
n number := 0;
begin
for f in 1..10000000
loop
n := mod (n,999999) + sqrt (f);
end loop;
end;I’ve called the procedure cpu_speed_interpreted as I’ve compile it in a standard way and with default values of plsql_optimize_level, and code will end up in interpreted state (parameter plsql_code_type).
select plsql_optimize_level, plsql_code_type
from all_plsql_object_settings where name='CPU_SPEED_INTERPRETED';
2 INTERPRETEDIn the next step I’ll create the same procedure with a higher optimization level and code will be natively compiled.
create or replace procedure cpu_speed_compiled
as
n number := 0;
begin
for f in 1..10000000
loop
n := mod (n,999999) + sqrt (f);
end loop;
end;
alter session set plsql_optimize_level = 3;
alter procedure cpu_speed_compiled compile plsql_code_type=native;
select plsql_optimize_level, plsql_code_type
from all_plsql_object_settings where name='CPU_SPEED_INTERPRETED';
3 NATIVENow it’s a time to get more information about the CPU.
username@domain.com:~>lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
Address sizes: 39 bits physical, 48 bits virtual
CPU(s): 8
On-line CPU(s) list: 0-7
Thread(s) per core: 2
Core(s) per socket: 4
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 158
Model name: Intel(R) Xeon(R) CPU E3-1505M v6 @ 3.00GHz
Stepping: 9
CPU MHz: 856.179
CPU max MHz: 4000,0000
CPU min MHz: 800,0000
...As the test is single threaded, the most important to note is the CPU information about a clock speed, which in this case is 3.00 GHz
1.
Finally I can execute the first test.
exec cpu_speed_interpreted;
6.916 sec
exec cpu_speed_compiled;
7.021 sec Interesting enough, for this case, native speed is a little bit slower when comparing with interpreted result.
Oracle 11g Rel 2 database was used to perform both tests.
2.
The second test I’m going to perform on my older laptop, so first I’ll get the info about a CPU.
username@domain.com:~>lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
Address sizes: 36 bits physical, 48 bits virtual
CPU(s): 4
On-line CPU(s) list: 0-3
Thread(s) per core: 2
Core(s) per socket: 2
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 42
Model name: Intel(R) Core(TM) i5-2520M CPU @ 2.50GHz
Stepping: 7
CPU MHz: 941.805
CPU max MHz: 3200,0000
CPU min MHz: 800,0000
...This time I’m getting the following results:
exec cpu_speed_interpreted;
10.94 sec
exec cpu_speed_compiled;
9.239 secIt’s a 58% increase in elapsed time (interpreted result) although the CPU speed is only 20% slower.
This time natively compiled PL/SQL code is also faster.
3.
If I repeat both tests on Oracle 18c, I’m getting the following results:
exec cpu_speed_interpreted;
5.932 sec
exec cpu_speed_compiled;
5.501 sec After performing 18c tests, we can conclude the following:
- By simply upgrading from the 11g to 18c, I’m getting almost 17% improved elapsed time in case of interpreted code (from 6.916 sec in 11g to 5.932 sec in 18c) and 28% improved time in case of native code (from 7.021 sec in 11g to 5.501 sec in 18c).
- It seems that native compilation of PL/SQL code is improved over releases, as native PL/SQL code always runs faster when comparing it with interpretive code.
- Generally, although native compilation of PL/SQL code brings improvement in case of CPU intensive tasks, performance boost is definitely far below expectations.
4.
Let’s see what will happen in case I’m using Java Stored Procedure in native compilation mode.
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Cpu_Speed_Java"
as
import java.lang.math.*;
public class Cpu_Speed_Java {
public static void speed() {
double n;
for(int f = 1; f <= 10000000; f++ ){
n = f % 999999 + Math.sqrt(f);
}
}
}
create or replace procedure Cpu_Speed_Java_Wrapper is language java
name 'Cpu_Speed_Java.speed()';
When I execute the code on Intel(R) Xeon(R) CPU E3-1505M v6 @ 3.00GHz machine (test 1), I’m getting the following results:
exec Cpu_Speed_Java_Wrapper;
0,068 sec If I repeat the same test on Intel(R) Core(TM) i5-2520M CPU @ 2.50GHz
exec Cpu_Speed_Java_Wrapper;
0.071 secIn case of using Java Stored Procedure to perform CPU intensive tasks, we can conclude the following:
- I’m getting 10070% or two orders of magnitude better performance (from 6.916 sec in case of interpreted code in 11g to 0.068 sec on the same 11g database in case of Java Stored Procedures).
- There are almost no difference in execution speed between Xeon 3.00 GHz and ancient Intel Core i5 2.50 GHz hardware (0.068 vs 0.071 sec).
Summary:
In this article I covered the following:
- How to perform single-threaded CPU reference test you can use to compare hardware speed from the Oracle Database perspective.
- As your CPU is faster (ancient Intel Core i5 2.50 GHz upgraded to Intel Xeon 3.00 GHz) you can get 37% better performances (interpreted PL/SQL result for CPU intensive tasks) although the CPU clock speed is only 20% faster.
- Performance issue with native compilation of PL/SQL code in 11g version of the Oracle database (11g is the first version where you can compile PL/SQL code without using external compiler) for performing CPU intensive tasks is fixed in later releases of Oracle database (e.g. 18c).
- Execution performance of natively compiled PL/SQL code when performing CPU intensive tasks, even in 18c release of the Oracle database, is far below expectations.
- Performance boost (17% in case of PL/SQL interpreted code) you can get by simply upgrading your Oracle database from 11g to a later release (18c is used in this article).
- Java Stored Procedures come to rescue to fix native compilation issue for all kind of CPU tasks is to use Java Store Procedures, as it’s working equally well in 11g and 18c version, and it brings expected performance boost of two orders of magnitudes (6.916 sec –> 0.068 sec).
As you can see, the best way to speed up your code for CPU intensive tasks is to use Java Stored Procedures.
Additionally you can note that hardware (CPU) upgrade is just one from several available options.
In many cases you won’t get a feeling of any performance improvement at all, as this case is unrealistic (I’m comparing old Intel Core i5 with pretty new Intel Xeon).
Instead of looking at HW upgrades, you might be better to look at simple database upgrade path, as you might get better performance (17% in this case) and no investment is needed.
If you are interested to further explore this topic, you might like my older article:
PL/SQL vs Java vs C vs Python for CPU intensive tasks – architectural decision
and
PL/SQL vs Java for Data intensive tasks
https://www.josip-pojatina.com/en/plsql-vs-java-for-data-intensive-tasks/
Comments