SQL, PL/SQL and JavaScript running in the Database Server (Oracle MLE)
7 min readMar 17, 2019
In a previous post I measured the CPU usage when running a database transaction in the same engine (SQL), or two engines in the same process (PL/SQL + SQL or JavaScript + SQL) or two processes (Javascript client + server SQL):
For the JavaScript + SQL running in the same process, I used the Oracle Multi-Lingual Engine in beta 0.2.7 but there is now a new beta 0.3.0 and this post runs the same (or similar) with this.
I’ve installed this MLE in a previous post:
And here is the demo where I run 400000 amount transfers between accounts. Here are the tables:
SQLcl: Release 18.4 Production on Sun Mar 17 15:42:34 2019
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sun Mar 17 2019 15:42:36 +00:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0_MLE - 64bit Beta15:42:39 SQL> create table CUSTOMERS (
CUSTOMER_ID number generated always as identity
constraint CUSTOMER_PK primary key,
CUSTOMER_NAME varchar2(42)
);Table created.15:42:42 SQL> create table ACCOUNTS (
ACCOUNT_ID varchar2(10) constraint ACCOUNT_PK primary key,
CUSTOMER_ID number,
AMOUNT number default 0
);Table created.15:42:46 SQL> insert /*+ append */ into CUSTOMERS (CUSTOMER_NAME)
select x from (
select to_char( date'-4712-01-01'+rownum-1,'Jsp') x
from xmltable('1 to 1000000')
) where length(x)=42 and rownum<=4000;4000 rows created.15:42:49 SQL> commit;Commit complete.15:42:51 SQL> select * from CUSTOMERS
order by CUSTOMER_ID fetch first 10 rows only; CUSTOMER_ID CUSTOMER_NAME
----------- ------------------------------------------
1 Three Thousand Three Hundred Seventy-Three
2 Three Thousand Three Hundred Seventy-Seven
3 Three Thousand Three Hundred Seventy-Eight
4 Three Thousand Seven Hundred Seventy-Three
5 Three Thousand Seven Hundred Seventy-Seven
6 Three Thousand Seven Hundred Seventy-Eight
7 Three Thousand Eight Hundred Seventy-Three
8 Three Thousand Eight Hundred Seventy-Seven
9 Three Thousand Eight Hundred Seventy-Eight
10 Seven Thousand Three Hundred Seventy-Three10 rows selected.15:42:54 SQL> insert /*+ append */ into ACCOUNTS
(ACCOUNT_ID,CUSTOMER_ID,AMOUNT)
select 'X'||to_char(rownum,'FM0999999'),CUSTOMER_ID,10000
from CUSTOMERS cross join xmltable('1 to 100')
;400000 rows created.15:42:57 SQL> commit;Commit complete.15:42:58 SQL> commit;Commit complete.15:43:15 SQL> select * from ACCOUNTS
order by ACCOUNT_ID fetch first 10 rows only;ACCOUNT_ID CUSTOMER_ID AMOUNT
---------- ----------- ------
X0000001 1150 10000
X0000002 1151 10000
X0000003 1152 10000
X0000004 1153 10000
X0000005 1154 10000
X0000006 1155 10000
X0000007 1156 10000
X0000008 1157 10000
X0000009 1158 10000
X0000010 1159 1000010 rows selected.15:43:16 SQL> select /*+ full(ACCOUNTS) cache(ACCOUNTS) */
count(*),avg(amount) from ACCOUNTS; COUNT(*) AVG(AMOUNT)
-------- -----------
400000 10000
I have a ‘show-cpu-seconds-from-ps.sh’ script that displays the cputime delta from ps output.
Here I run all in one SQL statement: 5 seconds of CPU
15:44:23 SQL> set timing on
15:44:28 SQL> host sh /tmp/sqlcl/show-cpu-seconds-from-ps.sh init to reset cputime counter15:44:30 SQL> update ACCOUNTS set AMOUNT=
2 case
3 when ACCOUNT_ID='X0000001' then AMOUNT+(select 1*count(*) from ACCOUNTS where ACCOUNT_ID<>'X0000001')
4 else AMOUNT-1
5 end
6 /400000 rows updated.Elapsed: 00:00:04.451
15:44:43 SQL> host sh /tmp/sqlcl/show-cpu-seconds-from-ps.sh diff to show the delta cputime
5 cpu seconds in pid= 19971 oracleCDB1 (LOCAL=NO)15:44:43 SQL> select * from ACCOUNTS order by ACCOUNT_ID fetch first 10 rows only;
ACCOUNT_ID CUSTOMER_ID AMOUNT
X0000001 1150 409999
X0000002 1151 9999
X0000003 1152 9999
X0000004 1153 9999
X0000005 1154 9999
X0000006 1155 9999
X0000007 1156 9999
X0000008 1157 9999
X0000009 1158 9999
X0000010 1159 999910 rows selected.Elapsed: 00:00:00.019
15:44:43 SQL> rollback;Rollback complete.Elapsed: 00:00:04.158
This is the actual CPU cycles needed to update those 400000 account amounts: 5 seconds. And the rollback is the same.
Now with a PL/SQL procedure: 30 seconds of CPU (because of the context switches between the PL/SQL and SQL engines)
15:44:47 SQL> create or replace procedure transfer(acc1 varchar2, acc2 varchar2, amount number) as
2 begin
3 -- debit acc1
4 update ACCOUNTS set ACCOUNTS.AMOUNT = ACCOUNTS.AMOUNT - transfer.amount where ACCOUNT_ID=acc1;
5 if sql%rowcount <> 1 then raise_application_error(-20000,'Account '''||acc1||''' unknown'); end if;
6 -- credit acc2
7 update ACCOUNTS set ACCOUNTS.AMOUNT = ACCOUNTS.AMOUNT + transfer.amount where ACCOUNT_ID=acc2;
8 if sql%rowcount <> 1 then raise_application_error(-20000,'Account '''||acc2||''' unknown'); end if;
9 end;
10 /Procedure created.
Elapsed: 00:00:00.113
15:46:11 SQL> desc transferPROCEDURE transfer
Argument Name Type In/Out Default?
ACC1 VARCHAR2 IN
ACC2 VARCHAR2 IN
AMOUNT NUMBER IN15:46:38 SQL> set timing on
15:46:41 SQL> host sh /tmp/sqlcl/show-cpu-seconds-from-ps.sh init to reset cputime counter15:46:43 SQL> exec for c in (select * from ACCOUNTS where ACCOUNT_ID<>'X0000001') loop transfer(c.ACCOUNT_ID,'X0000001',1); end
loop;PL/SQL procedure successfully completed.Elapsed: 00:00:30.283
15:47:15 SQL> host sh /tmp/sqlcl/show-cpu-seconds-from-ps.sh diff to show the delta cputime
30 cpu seconds in pid= 19971 oracleCDB1 (LOCAL=NO)15:47:38 SQL> select * from ACCOUNTS order by ACCOUNT_ID fetch first 10 rows only;
ACCOUNT_ID CUSTOMER_ID AMOUNT
X0000001 1150 409999
X0000002 1151 9999
X0000003 1152 9999
X0000004 1153 9999
X0000005 1154 9999
X0000006 1155 9999
X0000007 1156 9999
X0000008 1157 9999
X0000009 1158 9999
X0000010 1159 999910 rows selected.Elapsed: 00:00:00.015
15:47:43 SQL> rollback;Rollback complete.Elapsed: 00:00:04.266
Now with a JavaScript client: 157 seconds of CPU (in the same database session process, but two engines).
15:48:38 SQL> disconnectDisconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0_MLE - 64bit Beta
15:48:54 SQL> script
2 var DriverManager = Java.type("java.sql.DriverManager");
3 if ( ! con === undefined ) { con.rollback(); con.close(); }
4 var con = DriverManager.getConnection("jdbc:oracle:thin:@//localhost/PDB1","demo","demo");
5 con.setAutoCommit(false);
6 var sql = con.createStatement();
7 .15:49:10 SQL> save script01-init.js replace
Wrote file script01-init.js
15:49:16 SQL> @ script01-init.js
Elapsed: 00:00:01.019
15:49:18 SQL> script
2 print("First 10 accounts:");
3 var res=sql.executeQuery(" select ACCOUNT_ID,AMOUNT from ACCOUNTS order by 1 fetch first 10 rows only");
4 while(res.next()){print(" ACCOUNT_ID: "+res.getString(1)+" "+"AMOUNT: "+res.getString(2)); }
5 .15:49:33 SQL> save script02-query.js replace
Wrote file script02-query.js
15:49:35 SQL> @ script02-query.js
First 10 accounts:
ACCOUNT_ID: X0000001 AMOUNT: 10000
ACCOUNT_ID: X0000002 AMOUNT: 10000
ACCOUNT_ID: X0000003 AMOUNT: 10000
ACCOUNT_ID: X0000004 AMOUNT: 10000
ACCOUNT_ID: X0000005 AMOUNT: 10000
ACCOUNT_ID: X0000006 AMOUNT: 10000
ACCOUNT_ID: X0000007 AMOUNT: 10000
ACCOUNT_ID: X0000008 AMOUNT: 10000
ACCOUNT_ID: X0000009 AMOUNT: 10000
ACCOUNT_ID: X0000010 AMOUNT: 10000
Elapsed: 00:00:00.181
15:49:37 SQL> script
2 var pre1=con.prepareStatement(" update ACCOUNTS set AMOUNT=AMOUNT-? where ACCOUNT_ID=?");
3 var pre2=con.prepareStatement(" update ACCOUNTS set AMOUNT=AMOUNT+? where ACCOUNT_ID=?");
4 function transfer (acc1,acc2,amount) {
5 pre1.setInt(1,amount); pre1.setString(2,acc1); pre1.execute();
6 pre2.setInt(1,amount); pre2.setString(2,acc2); pre2.execute();
7 }
8 var res=sql.executeQuery(" select ACCOUNT_ID from ACCOUNTS where ACCOUNT_ID<>'X0000001'");
9 print("Calling transaction for each account...");var t0=new Date();var cnt=0;
10 while(res.next()){ transfer(res.getString(1),'X0000001',1); cnt++ }
11 print(cnt+" transactions executed in "+(new Date() - t0)/1000+" seconds");
12 .15:50:17 SQL> save script02-run.js replace
Wrote file script02-run.js
15:50:18 SQL> set timing on
15:50:22 SQL> host sh /tmp/sqlcl/show-cpu-seconds-from-ps.sh init to reset cputime counter15:50:25 SQL> @ script02-run.js
Calling transaction for each account...
399999 transactions executed in 138.016 seconds
Elapsed: 00:02:18.082
15:52:45 SQL> host sh /tmp/sqlcl/show-cpu-seconds-from-ps.sh diff to show the delta cputime
52 cpu seconds in pid= 19945 /opt/oracle/product/12.2.0.1/dbhome_1/jdk/jre/bin/java -Djava.awt.headless=true -Dappl
e.awt.UIElement=true -Xss10M -client
105 cpu seconds in pid= 20426 oracleCDB1 (LOCAL=NO)15:52:56 SQL> @ script02-query.js
First 10 accounts:
ACCOUNT_ID: X0000001 AMOUNT: 409999
ACCOUNT_ID: X0000002 AMOUNT: 9999
ACCOUNT_ID: X0000003 AMOUNT: 9999
ACCOUNT_ID: X0000004 AMOUNT: 9999
ACCOUNT_ID: X0000005 AMOUNT: 9999
ACCOUNT_ID: X0000006 AMOUNT: 9999
ACCOUNT_ID: X0000007 AMOUNT: 9999
ACCOUNT_ID: X0000008 AMOUNT: 9999
ACCOUNT_ID: X0000009 AMOUNT: 9999
ACCOUNT_ID: X0000010 AMOUNT: 9999
Elapsed: 00:00:00.015
15:53:13 SQL> script
2 con.rollback();
3 con.close();
4 .15:53:20 SQL> save script02-close.js replace
Wrote file script02-close.js
15:53:22 SQL> @ script02-close.js
Elapsed: 00:00:06.198
And finally running JavaScript in the MLE engine: 223 seconds of CPU. This MLE, in beta, may not be fully optimized, so the time is not very relevant. The point is that the whole is running 100% in the same process.
15:53:31 SQL> connect demo/demo@//localhost/pdb1
Connected.15:55:34 SQL> create or replace javascript source named "demo.js" as
2 function transfer (acc1,acc2,amount) {
3 var sql = _dbRequire('@oracle/sql');
4 sql.execute(" update ACCOUNTS set AMOUNT=AMOUNT-:amount where ACCOUNT_ID=:acc1",[amount,acc1]);
5 sql.execute(" update ACCOUNTS set AMOUNT=AMOUNT+:amount where ACCOUNT_ID=:acc2",[amount,acc2]);
6 }
7 module.exports.run = function () {
8 var sql = _dbRequire('@oracle/sql');
9 var res=sql.execute(" select ACCOUNT_ID from ACCOUNTS where ACCOUNT_ID<>'X0000001'");
10 for (var row of res.rows) {
11 transfer(row[0],'X0000001',1);
12 }
13 }
14 /Function created.
Elapsed: 00:00:00.013
15:56:02 SQL> create or replace procedure run as language javascript
2 name 'demo\.js.run()';
3 /Procedure created.
Elapsed: 00:00:00.032
15:56:14 SQL> select * FROM user_libraries;no rows selected
Elapsed: 00:00:00.122
15:56:19 SQL> select object_name,procedure_name,object_type from user_procedures;
OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE
RUN PROCEDURE
TRANSFER PROCEDUREElapsed: 00:00:00.291
15:56:21 SQL> select object_name,procedure_name,object_type from user_procedures;
OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE
RUN PROCEDURE
TRANSFER PROCEDUREElapsed: 00:00:00.012
15:56:36 SQL> set timing on
15:56:51 SQL> host sh /tmp/sqlcl/show-cpu-seconds-from-ps.sh init to reset cputime counter15:56:53 SQL> call demo.run();Call completed.Elapsed: 00:03:32.463
16:00:28 SQL> host sh /tmp/sqlcl/show-cpu-seconds-from-ps.sh diff to show the delta cputime
223 cpu seconds in pid= 20761 oracleCDB1 (LOCAL=NO)
The important point with this MLE engine is that you can write your code without being tied to a platform. You write code (or use libraries of code, copy/paste from StackOverflow…) in the latest trendy languages (JavaScript and Python for the moment, whatever in the future). And it can run on the client, the application server, or in the database. Then, the best colocation of code can be achieved without duplicating the logic into different languages. In summary, the developer thinks “serverless” for simplicity and agility and the operations run “full server” for efficiency and scalability.
But that’s for the future. Follow the MLE and be involved in the community:
For your curiosity, where is the perf-top for the last run in MLE showing the work in oracle and in libmle.so engines:
