Oracle Database MLE JavaScript Performance Analysis #JoelKallmanDay

Welcome to my first post on this blog. The goal of this post is to look into the performance implications of the newly introduced MLEs in Oracle 23ai. I can’t think of a better date to start this blog than on Joel Kallman Day! When I was an APEX rookie, Joel was a significant influence and provided a lot of helpful insights and ideas. Whenever someone in the community had a problem, Joel was the first to offer his help. To this day, I find myself in his blog posts every now and then to put his great content to use.

Introducing MLEs to the Oracle Database

Technically, MLEs are not new in 23ai but rather in 21c, where Oracle introduced DBMS_MLE to generate dynamically generated JavaScript code. This was already a significant advancement for developers, providing a third language alongside PL/SQL and the often-overlooked Java in the database. One major drawback of DBMS_MLE was its inability to store JavaScript modules in the database (comparable to PL/SQL packages or Java classes). Developers always had to execute JavaScript via PL/SQL. This also hindered code reuse greatly, one of the major advantages of JavaScript due to its large open-source community.

23ai brings the ability to store modules and call them with the help of PL/SQL wrappers, just like Java methods. You can also reference and import existing JavaScript modules to reuse implementations and take advantage of the JavaScript ecosystem. Philipp Salvisberg describes this as “the best thing that happened to the Oracle Database since version 7, which brought us PL/SQL” and also points out how to effectively take advantage of existing TypeScript Toolchains to develop JavaScript code for the Oracle Database. I’m inclined to agree! When I look at the tooling, development and deployment practices in client projects, I cannot help but scratch my head and wonder how the last 20 to 30 years of software development enhancements have been unnoticed for so long by so many Oracle developers. Just to give you an idea of what I am talking about:

• I have yet to meet a customer who uses unit tests at any meaningful scale
• Most of the time, version control is lackluster at best, if used at all
• If version control is used, you better check if it is in sync with prod before you deploy your changes…
• DB Schema are mostly done manually or via one time deployment scripts. Change management tools like Liquibase or Flyway are sporadically used (if you are lucky)

These are just the tip of the iceberg and of course they cannot be fixed just by enabling developers to use JavaScript. But the tooling and the community is so vastly different and more mature that it might very well rub off on Oracle Database development practices.

Why benchmark JavaScript?

Now that we got this out of the way, let’s get to the fun part: Dunking on JavaScript for its lackluster performance. The internet is full of memes and blog posts saying that it was one of the worst ideas ever to introduce JavaScript to the backend. This, of course, is mostly for comedic purposes but has some truth to it when you look at raw performance. JavaScript was never designed to be the fastest language out there. But when you use a programming language at the server-side, inevitably, you will find yourself trying to squeeze every last drip of performance out of it. We still have PL/SQL and Java to help out when we need them to but the question is: Just how fast (or slow) is JavaScript in the Oracle Database? Can it be a replacement for PL/SQL entirely or do we have to choose JavaScript workloads carefully?

To answer these questions, I have prepared a few scenarios that mimic some everyday tasks PL/SQL developers are faced with and put them to the test in both PL/SQl and JavaScript. These are in no way representative of all features both languages have to offer but they will give us some idea of the overall performance difference.

Test Scenarios

The following scenarios will be tested:

1. Simple arithmetic operations
2. Simple DML
3. Simple DML (amplified)

Simple Arithmetic Operations

First, we will take a look at arithmetic operations that could be performed independently of any database. The choice of specific operations is arbitrary but should give us a rough idea on the performance difference:

create or replace function simple_performance_plsql(
    i_starting_number number
    ,i_iterations number
) 
return number as

    l_result number := i_starting_number;
begin
    for i in 1..i_iterations loop
        l_result := l_result + i_starting_number;
		l_result := l_result - i_starting_number / 3;
    end loop;

    return l_result;
end simple_performance_plsql;
/

When we want to implement the same thing in JavaScript, we have to jump through a few more hoops. If you want to dive into what exactly needs to be done, I recommend this blog post. For now, I will just provide the code.

First, create the JavaScript module:

create or replace mle module simple_performance_module
language javascript as
function simple_performance_js(startingNumber, iterations) {
    let result = startingNumber;
    for (let i = 0; i < iterations; i++) {
        result += startingNumber;
        result -= startingNumber / 3;
    }
    return result;
}
export { simple_performance_js }

Secondly, create the wrapper:

create or replace function simple_performance_js_wrapper(
    i_starting_number number
    ,i_iterations number
) 
return number as
  mle module
  simple_performance_module signature 'simple_performance_js(number, number)';

To compare the performance, I will run both functions back-to-back while measuring the elapsed CPU time. The first test will be with a number that is divisible by 3:

set serverout on

create or replace procedure compare_arithmetic_performance(
    i_starting_number number
    ,i_iterations number
) 
as
    l_result number;
    l_cpu_start_time integer;
begin
    l_cpu_start_time := dbms_utility.get_cpu_time();

    l_result := simple_performance_plsql(i_starting_number, i_iterations);

    dbms_output.put_line(l_result);
    dbms_output.put_line(
        'PL/SQL took ' || to_char(dbms_utility.get_cpu_time() - l_cpu_start_time) || ' hsecs'
    );

    l_cpu_start_time := dbms_utility.get_cpu_time();

    l_result := simple_performance_js_wrapper(i_starting_number, i_iterations);

    dbms_output.put_line(l_result);
    dbms_output.put_line(
        'JavaScript took ' || to_char(dbms_utility.get_cpu_time() - l_cpu_start_time) || ' hsecs'
    );
end;
/

begin
    compare_arithmetic_performance(9, 100000000);
end;
/

My output looks like this:

600000009
PL/SQL took 226 hsecs
600000009
JavaScript took 431 hsecs

We can see that the result is the same. This is just a basic sanity check in case I screwed up the implementation. The timings are much more interesting: JavaScript took around double the time compared to a pure PL/SQL implementation. Is this just because of the context switch for the PL/SQL wrapper around the JS module? We can find it out by simply increasing the number of iterations:

begin
    compare_arithmetic_performance(9, 1000000000);
end;

Which gives me this:

6000000009
PL/SQL took 2285 hsecs
6000000009
JavaScript took 4347 hsecs

JS is still roughly takes double the time. The context switch does not seem to be the issue. Instead, JS is just half as fast when it comes to number crunching. But: What happens when we replace the starting number 9 with 10? 10 is not divisible by 3 and actually paints a different picture:

begin
    compare_arithmetic_performance(10, 100000000);
end;

Now the difference is much smaller:

666666676,666666666666666666666695049506
PL/SQL took 330 hsecs
666666676,533024
JavaScript took 431 hsecs

Also note, that the results differ because of the different number representations in PL/SQl and JavaScript.

Now I hear the PL/SQL folks say: Why do you use number when you could use binary_integer and binary_double? And they are right. For purely mathematical purposes, you should always use these data types. They change the results again:

create or replace function simple_performance_plsql(
    i_starting_number binary_integer
    ,i_iterations binary_integer
) 
return binary_double as

    l_result binary_double := i_starting_number;

begin
    for i in 1..i_iterations loop
        l_result := l_result + i_starting_number;
		l_result := l_result - i_starting_number / 3;
    end loop;

    return l_result;
end simple_performance_plsql;
/

I could also change the comparison function, but I am too lazy, and PL/SQL still takes a huge win:

begin
    compare_arithmetic_performance(9, 100000000);
end;

Result:

600000009
PL/SQL took 121 hsecs
600000009
JavaScript took 431 hsecs

The same is true for numbers not divisible by 3:

begin
    compare_arithmetic_performance(10, 100000000);
end;

Result:

666666676,53302372
PL/SQL took 122 hsecs
666666676,533024
JavaScript took 434 hsecs

Unfortunately, I cannot try JavaScript with binary_integer because the data type is not supported yet:

ORA-04162: Unsupported type binary_integer in signature clause of MLE call specification

But it shouldn’t make a big difference since we already established that the switching (and conversion) overhead does not make a huge dent.

Simple DML

This first test showed a big difference in raw computational performance between PL/SQL and JavaScript. But how about some simple DML statements? PL/SQl was literally designed to incorporate SQL in its programs. It’s in the name… How does this compare to JavaScript?

First, let’s prepare the test:

create table test_table(col1 varchar2(100));
/

Next, we create a very simple PL/SQL procedure:

create or replace procedure simple_select_performance as
    l_test test_table.col1%type;
begin
    insert into test_table(col1)
    values('Test');

    select col1
    into l_test
    from test_table fetch first 1 rows only;

    update test_table
    set col1 = 'Updated Test';

    delete from test_table;
end simple_select_performance;
/

And also create the corresponding JavaScript:

create or replace mle module simple_select_performance_module
language javascript AS
function simple_select_performance_js() {
    let connection;

    connection = oracledb.defaultConnection();

    let result = connection.execute(`
        insert into test_table(col1)
        values('Test')`
    );

    result = connection.execute(`
        select col1
    from test_table fetch first 1 rows only`
    );

    result = connection.execute(`
        update test_table
    set col1 = 'Updated Test'`
    );

    result = connection.execute(`
        delete from test_table`
    );
}
export { simple_select_performance_js }

create or replace procedure simple_select_performance_js
as mle module simple_select_performance_module
signature 'simple_select_performance_js';

We can compare the performance with this snippet:

set serverout on

declare
    l_start_time timestamp;
begin
    l_start_time := systimestamp;
    simple_select_performance;
    dbms_output.put_line(
        'PL/SQL took ' || extract( second from (systimestamp - l_start_time) ) || ' seconds'
    );

    l_start_time := systimestamp;
    simple_select_performance_js;
    dbms_output.put_line(
        'JavaScript took ' || extract( second from (systimestamp - l_start_time) ) || ' seconds'
    );
end;

This gives me the following results:

PL/SQL took ,000184 seconds
JavaScript took ,000593 seconds

Which is … not that surprising. PL/SQL should handle this piece of cake just fine and JS took 3 times as long but still a tiny fraction of a second (~6ms). However, these results vary quite a bit. Most of the time, the implementations are on par on terms of speed. I included this test for completeness but the interesting part starts when we compare a large number of those statements, which is exactly what we’re going to do!

Simple DML (amplified)

In this section we want to get to the heart of it: How does JS integrate with the Oracle Database and its tables. When we optimize PL/SQL we often speak about context switches. This means the context switch from executing PL/SQL to executing SQL and then back to PL/SQL (or vice-versa). PL/SQL developers understand this as something “expensive” that should be minimized. However, PL/SQL was designed to still allow these context switches to happen as fast as possible. Where does JavaScript stand on this issue?

Again, we first create the PL/SQL template:

insert into test_table (col1)
values ('Test');

create or replace procedure loop_select_performance as
    l_test test_table.col1%type;
begin

    for i in 1..100000 loop
        select col1
        into l_test
        from test_table fetch first 1 rows only;

        update test_table
            set col1 = l_test;
    end loop;

end loop_select_performance;
/

…and create the same thin in JavaScript:

create or replace mle module loop_select_performance_module
language javascript AS
function loop_select_performance_js() {
    let connection;
    let result;

    connection = oracledb.defaultConnection();

    for (let i = 0; i < 100000; i++) {
        result = connection.execute(`
        select col1
        from test_table fetch first 1 rows only`
        );

        connection.execute(`update test_table
            set col1 = :val`, [result.rows[0].COL1]);
    }

}
export { loop_select_performance_js }

create or replace procedure loop_select_performance_js
as mle module loop_select_performance_module
signature 'loop_select_performance_js';

And finally, test the whole thing again:

set serverout on

declare
    l_start_time integer;
begin
    l_start_time := dbms_utility.get_cpu_time();
    loop_select_performance;
    dbms_output.put_line(
        'PL/SQL took ' || to_char(dbms_utility.get_cpu_time() - l_start_time) || ' hsecs'
    );

    l_start_time := dbms_utility.get_cpu_time();
    loop_select_performance_js;
    dbms_output.put_line(
        'JavaScript took ' || to_char(dbms_utility.get_cpu_time() - l_start_time) || ' hsecs'
    );
end;

Let’s look at the results:

PL/SQL took 253 hsecs
JavaScript took 1545 hsecs

Oh my…

First of all, of course, this is not the most efficient way to handle this workload. I just want to perform a bunch of select and update. But the difference is immense. JavaScript takes more than 6 times as long for the same workload. Now that is a context switch. This should not come as a surprise, since we have to go the standard JavaScript way of going through a connection object to access the Oracle Database.

Conclusion

This was not a blog post dunking on JavaScript, even though it might have read that way at times. As an APEX developer, I use JavaScript everyday and I am happy with its state. It was a great decision from Oracle to introduce it to the database and I hope that many other languages will follow (I am looking at you, Python…).

Instead, I wanted to show the limitations of JavaScript in the database to let developers make informed decisions when to use it and when to resort to PL/SQL. I have spoken to many customers who were excited about MLE and JavaScript in the Oracle Database. They were hopeful that PL/SQL might be no longer needed to write stored procedures. This is obviously not the case, if you want your users to stay but it opens up a great opportunity for some workloads that are not as time-critical or data-/computationally-intense. And, the most important thing: It gives us easy access to all those great open-source JavaScript projects directly in the database!