{"id":10531,"date":"2017-10-11T06:35:00","date_gmt":"2017-10-11T04:35:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-database-multilingual-engine-mle\/"},"modified":"2017-10-11T06:35:00","modified_gmt":"2017-10-11T04:35:00","slug":"oracle-database-multilingual-engine-mle","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-database-multilingual-engine-mle\/","title":{"rendered":"Oracle Database Multilingual Engine (MLE)"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nMy ODC appreciation blog post<\/a> was about Javascript in the database running in the beta of the Oracle Database Multilingual Engine (MLE). Here I’ll detail my first test which is a comparison, in performance, between a package written in Javascript, running in the MLE, and one written and running in PL\/SQL.
\n
\nI’ve downloaded the 12GB .ova from OTN<\/a>, installed the latest SQLcl<\/a>, and I’m ready to load my first Javascript procedure. I want something simple that I can run a lot of times because I want to test my main concern when running code in a different engine: the context switch between the SQL engine and the procedural one.<\/p>\n

My kid’s maths exercises were about GCD (greatest common divisor) this week-end so I grabbed the Euclid’s algorithm in Javascript<\/a>. This algorithm was the first program I ever wrote long time ago, on ZX-81, in BASIC. Now in Javascript it can use recursion. So here is my gcd.js file:<\/p>\n

\nmodule.exports.gcd = function (a, b) {\n function gcd(a, b) {\n if (b == 0)\n   {return a}\n else\n   {return gcd(b, a % b)}\n }\nreturn gcd(a,b)\n}\n<\/code><\/pre>\n
We need strong typing to be able to load it as a stored procedure, so here is the TypeScript definition in gcd.d.ts<\/p>\n
\nexport function gcd(a:number, b:number ) : number;\n<\/code><\/pre>\n
I load it with the dbjs utility, which I run in verbose mode:<\/p>\n
\n[oracle@dbml MLE]$ dbjs deploy -vv gcd.js -u demo -p demo -c \/\/localhost:1521\/DBML\ndeploy: command called  \/media\/sf_share\/MLE\/gcd.js oracle\nOracle backend: starting transpiler\ngcd: processed function\nOracle backend: opening connection to database\ngcd.js: retrieving functions\ndropModule: called with  gcd.js\nloadModule: called with gcd.js\nBEGIN\nEXECUTE IMMEDIATE 'CREATE PACKAGE GCD AS\nFUNCTION GCD(\"p0\" IN NUMBER, \"p1\" IN NUMBER) RETURN NUMBER AS LANGUAGE JS LIBRARY \"gcd.js\" NAME \"gcd\" PARAMETERS(\"p0\" DOUBLE, \"p1\" DOUBLE);\nEND GCD;';\nEND;\n: generated PLSQL\n+ gcd.js\n\u2514\u2500\u252c gcd\n  \u2514\u2500\u2500 SCALAR FUNCTION GCD.GCD(\"p0\" IN NUMBER, \"p1\" IN NUMBER) RETURN NUMBER\n<\/code><\/pre>\n
As it is mentioned in the verbose log, the Javascript code is transpiled. My guess is that the Javascript is parsed by the Oracle Truffle framework and compiled by Oracle GaalVM. More info in the One VM to Rule Them All<\/a> paper. <\/p>\n
This has loaded the package, the library and an ‘undefined’ object of type 144 (this MLE is in beta so not all dictionary views have been updated):<\/p>\n
\nSQL> select * from dba_objects where owner='DEMO';\n \nOWNER   OBJECT_NAME   SUBOBJECT_NAME   OBJECT_ID   DATA_OBJECT_ID   OBJECT_TYPE   CREATED                LAST_DDL_TIME          TIMESTAMP             STATUS   TEMPORARY   GENERATED   SECONDARY   NAMESPACE   EDITION_NAME   SHARING   EDITIONABLE   ORACLE_MAINTAINED\n-----   -----------   --------------   ---------   --------------   -----------   -------                -------------          ---------             ------   ---------   ---------   ---------   ---------   ------------   -------   -----------   -----------------\nDEMO    GCD                                  93427                  PACKAGE       09-OCT-2017 15:29:33   09-OCT-2017 15:29:33   2017-10-09:15:29:33   VALID    N           N           N                     1                NONE      Y             N\nDEMO    gcd.js                               93426                  LIBRARY       09-OCT-2017 15:29:33   09-OCT-2017 15:29:33   2017-10-09:15:29:33   VALID    N           N           N                     1                NONE      Y             N\nDEMO    gcd.js                               93425                  UNDEFINED     09-OCT-2017 15:29:33   09-OCT-2017 15:29:33   2017-10-09:15:29:33   VALID    N           N           N                   129                NONE                    N\n \n \nSQL> select * from sys.obj$ where obj# in (select object_id from dba_objects where owner='DEMO');\n \nOBJ#    DATAOBJ#   OWNER#   NAME         NAMESPACE   SUBNAME   TYPE#   CTIME                  MTIME                  STIME                  STATUS   REMOTEOWNER   LINKNAME   FLAGS   OID$   SPARE1   SPARE2   SPARE3   SPARE4   SPARE5   SPARE6   SIGNATURE                          SPARE7   SPARE8   SPARE9\n----    --------   ------   ----         ---------   -------   -----   -----                  -----                  -----                  ------   -----------   --------   -----   ----   ------   ------   ------   ------   ------   ------   ---------                          ------   ------   ------\n  93427                 284 GCD                    1                 9 09-OCT-2017 15:29:33   09-OCT-2017 15:29:33   09-OCT-2017 15:29:33          1                                0               6    65535      284                            51713CBD7509C7BDA23B4805C3E662DF          0        0        0\n  93426                 284 gcd.js                 1                22 09-OCT-2017 15:29:33   09-OCT-2017 15:29:33   09-OCT-2017 15:29:33          1                                0               6    65535      284                            8ABC0DDB16E96DC9586A7738071548F0          0        0        0\n  93425                 284 gcd.js               129               144 09-OCT-2017 15:29:33   09-OCT-2017 15:29:33   09-OCT-2017 15:29:33          1                                0               6    65535      284                                                                      0        0        0\n<\/code><\/pre>\n
MLE Javascript<\/h3>\n
So, I’ve executed the function multiple times for each one of 10 millions rows:<\/p>\n
\nSQL> select distinct gcd(rownum,rownum+1),gcd(rownum,rownum+2),gcd(rownum,rownum+3) from xmltable('1 to 10000000');\n \nElapsed: 00:00:17.64\n<\/code><\/pre>\n
The execution on 30 million took 17 seconds<\/p>\n
PL\/SQL function<\/h3>\n
In order to compare, I’ve created the same in PL\/SQL:<\/p>\n
\nSQL> create or replace function gcd_pl(a number, b number) return number as\n  2   function gcd(a number, b number) return number is\n  3   begin\n  4     if b = 0 then\n  5        return a;\n  6     else\n  7        return gcd_pl.gcd(b,mod(a,b));\n  8     end if;\n  9   end;\n 10   begin\n 11     return gcd_pl.gcd(a,b);\n 12   end;\n 13  \/\n<\/code><\/pre>\n
Here is the execution:<\/p>\n
\nSQL> select distinct gcd_pl(rownum,rownum+1),gcd_pl(rownum,rownum+2),gcd_pl(rownum,rownum+3) from xmltable('1 to 10000000');\n \nElapsed: 00:01:21.05\n<\/code><\/pre>\n
PL\/SQL UDF function<\/h3>\n
In 12c<\/i> we can declare a function with the pragma UDF so that it is optimized for calling from SQL<\/p>\n
\nSQL> create or replace function gcd_pl_udf(a number, b number) return number as\n  2   pragma UDF;\n  3   function gcd(a number, b number) return number is\n  4   begin\n  5     if b = 0 then\n  6        return a;\n  7     else\n  8        return gcd_pl_udf.gcd(b,mod(a,b));\n  9     end if;\n 10   end;\n 11   begin\n 12     return gcd_pl_udf.gcd(a,b);\n 13   end;\n 14  \/\n<\/code><\/pre>\n
Here is the execution:<\/p>\n
\nSQL> select distinct gcd_pl_udf(rownum,rownum+1),gcd_pl_udf(rownum,rownum+2),gcd_pl_udf(rownum,rownum+3) from xmltable('1 to 10000000');\n \nElapsed: 00:00:51.85\n<\/code><\/pre>\n
Native compilation<\/h3>\n
We can also improve PL\/SQL runtime by compiling it in native, rather than being interpreted on p-code<\/p>\n
\nSQL> alter session set plsql_code_type=native;\nSession altered.\n \nSQL> alter function gcd_pl_udf compile;\nFunction altered.\n \nSQL> alter function gcd_pl compile;\nFunction altered.\n<\/code><\/pre>\n
and here is the result:<\/p>\n
\nSQL> select distinct gcd_pl_udf(rownum,rownum+1),gcd_pl_udf(rownum,rownum+2),gcd_pl_udf(rownum,rownum+3) from xmltable('1 to 10000000');\n \nElapsed: 00:01:10.31\n \nSQL> select distinct gcd_pl_udf(rownum,rownum+1),gcd_pl_udf(rownum,rownum+2),gcd_pl_udf(rownum,rownum+3) from xmltable('1 to 10000000');\n \nElapsed: 00:00:45.54\n<\/code><\/pre>\n
Inline PL\/SQL<\/h3>\n
Finally, similar to an UDF function, we can declare the function in the query, inlined in a WITH clause:<\/p>\n
\nSQL> with function gcd_pl_in(a number, b number) return number as\n  2   function gcd(a number, b number) return number is\n  3   begin\n  4     if b = 0 then\n  5        return a;\n  6     else\n  7        return gcd(b,mod(a,b));\n  8     end if;\n  9   end;\n 10   begin\n 11     return gcd(a,b);\n 12   end;\n 13  select distinct gcd_pl_in(rownum,rownum+1),gcd_pl_in(rownum,rownum+2),gcd_pl_in(rownum,rownum+3) from xmltable('1 to 10000000')\n 14  \/\n<\/code><\/pre>\n
And here is the result:<\/p>\n
\nElapsed: 00:00:48.92\n<\/code><\/pre>\n
Elapsed time summary<\/h3>\n
Here is a recap of the elapsed time:
\n\"CaptureMLE\"<\/a><\/p>\n
Elapsed: 00:00:17.64 for MLE Javascript
\nElapsed: 00:00:45.54 for PL\/SQL UDF function (native)
\nElapsed: 00:00:48.92 for Inline PL\/SQL
\nElapsed: 00:00:51.85 for PL\/SQL UDF function (interpreted)
\nElapsed: 00:01:10.31 for PL\/SQL function (native)
\nElapsed: 00:01:21.05 for PL\/SQL function (interpreted)<\/p>\n
The top winner is Javascript!<\/p>\n
Perfstat Flame Graph<\/h3>\n
My tests were deliberately doing something we should avoid for performance and scalability: call a function for each row, because this involves a lot of time spent in switching the context between the SQL and the procedural engine. But this is however good for code maintainability. This overhead is not easy to measure from the database. We can look at the call stack to see what happens when the process is evaluating the operand (evaopn2) and switches to PL\/SQL (evapls), and what happens besides running the PL\/SQL itself (pfrrun). I have recorded perf-stat for the cases above to display the Flame Graph on the call stack. When looking for more information I remembered that Frits Hoogland already did that so I let you read Frits part1<\/a> and part2<\/a><\/p>\n
You can download<\/a> my Flame Graphs and here is a summary of .svg name and call stack from operand evaluation to PL\/SQL run:<\/p>\n
\nPL\/SQL UDF function (native)        perf-gcd_pl_UDF_native.svg       evaopn2>evapls>peidxrex>penrun\nInline PL\/SQL                       perf-gcd_pl_inline.svg           evaopn2>evapls>kkxmss_speedy_stub>peidxrex>pfrrun>pfrrun_no_tool\nPL\/SQL UDF function (interpreted)   perf-gcd_pl_UDF_interpreted.svg  evaopn2>evapls>peidxexe>pfrrun>pfrrun_no_tool\nPL\/SQL function (native)            perf-gcd_pl_native.svg           evaopn2>evapls>kgmexec>kkxmpexe>kkxdexe>peidxexe>peidxr_run>plsql_run>penrun\nPL\/SQL function (interpreted)       perf-gcd_pl_interpreted.svg      evaopn2>evapls>kgmexec>kkxmpexe>kkxdexe>peidxexe>peidxr_run>plsql_run>pfrrun>pfrrun_no_tool\n<\/code><\/pre>\n
But more interesting is the Flame Graph for the JavaScript execution:
\n\"CaptureMLEFlame\"<\/a><\/p>\n
My interpretation on this is limited but I don’t see a stack of context switching function before calling the MLE engine, which is probably the reason why it is fast. Besides the ‘unknown’ which is probably the run of the JavaScript itself (the libwalnut.so library has no symbols) we can see that most of the time is in converting SQL data types into JavaScript types at call, and the opposite on return:<\/p>\n