When browsing through the Oracle Database 23 features especially concerning the topics around PL/SQL, SQL and general performance I just found a very interesting new capability called SQL Transpiler. The name of the feature made me curious to find out more. As a result I put together information about it and illustrate the functionality and setup with a simple example.
The functionality is made for stored PL/SQL functions which are called and used in SQL queries. As you may know these use cases may result in some overhead. Context switches between SQL and PL/SQL engines occur, because the PL/SQL runtime must be invoked when the SQL is executed.
Let’s give an example. The following PL/SQL function called IS_PRESIDENT finds out if an employee from table EMP has the job role PRESIDENT. To accomplish that we only need to check the value of the column MGR. If the value is 1, the employee has the job role PRESIDENT.
create or replace function is_president(p_mgr number) return number as begin return (case when p_mgr is null then 1 else 0 end); end; /
Let’s use it in the WHERE clause of a query. As you can see the employee with ename KING has the job role PRESIDENT.
SQL> select ename, job from emp where is_president(mgr) = 1; ENAME JOB ---------- --------- KING PRESIDENT
Let’s have a look at the execution plan including the predicate information. We use DBMS_XPLAN.DISPLAY_CURSOR to display the explain plan of any cursor loaded in the cursor cache.
SQL> SET LINESIZE 130 PAGESIZE 0
SQL> select * from dbms_xplan.display_cursor();
SQL_ID 0n924y0tk6mrt, child number 0
-------------------------------------
select ename, job from emp where is_president (mgr) = 1
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 18 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("IS_PRESIDENT"("MGR")=1)
18 rows selected.
In the predicate information section there is a call to the PL/SQL function IS_PRESIDENT. There is a context switch between SQL and PL/SQL.
How can we avoid this? If you are already familiar with 21c, you could solve this with the SQL Macro technology. You could write a suitable SQL Macro function to circumvent this situation. In our example it could look like this.
create function my_is_president (p_mgr number)
return varchar2 SQL_MACRO(SCALAR)
as
begin
return('case when p_mgr is null then 1 else 0 end');
end;
/
If you are interested in SQL Macros, have a look at Database PL/SQL Language Reference.
Of course you could use the same technology in 23ai now. However in Oracle Database 23, Oracle goes one step further and introduces an automatic conversion with the SQL Transpiler. You only need to setup and enable the transpiler functionality with the new parameter SQL_TRANSPILER. No further manuel interaction is required. The SQL Transpiler is disabled by default. You can enable it with an ALTER SYSTEM or ALTER SESSION command and change the parameter value accordingly. When SQL_TRANSPILER is set to ON, the SQL transpiler feature is enabled and PL/SQL functions are automatically transpiled (converted) into SQL expressions whenever possible. When this parameter is set to OFF, the SQL Transpiler feature is disabled.
First let’s check the parameter value.
SQL> sho parameter sql_transpiler NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sql_transpiler string OFF
Let’s change the parameter to enable SQL Transpiler. You can find more about the parameter in Database Reference.
SQL> alter session set sql_transpiler=on; Session altered.
Now let’s execute the query again.
SQL> select ename, job from emp where is_president(mgr) =1; ENAME JOB ---------- --------- KING PRESIDENT
As before, we use DBMS_XPLAN.DISPLAY_CURSOR to find out more about the execution.
SQL> select * from dbms_xplan.display_cursor (); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID fn9sdk4fca4yh, child number 0 ------------------------------------- select ename, job from emp where is_president (mgr) =1 Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| EMP | 1 | 18 | 3 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(CASE WHEN "MGR" IS NULL THEN 1 ELSE 0 END =1) 18 rows selected.
The PL/SQL function call IS_PRESIDENT (MGR) is replaced with CASE WHEN “MGR” IS NULL THEN 1 ELSE 0 END as you can see in the Predicate Information section. This indicates that transpilation has occurred.
Please note: Not all PL/SQL constructs are supported by the transpiler in this release. There will be further enhancements and restrictions will be lifted. For example PL/SQL elements such as PLS_INTEGER, %TYPE, /%ROWTYPE variables, PL/SQL aggregate types or object types are not eligable for transpilation at the moment.
In addition here are some other limitations:
- Embedded SQL statements. A transpiled function cannot contain a cursor declaration, explicit cursors, ref cursors, or an execute-immediate statement
- Functions defined inside of a PL/SQL package.
- Package variables, both public and private.
- Locally defined (nested) functions.
- Transaction processing like COMMIT, ROLLBACK, LOCK-TABLE, PRAGMA AUTONOMOUS TRANSACTION, SELECT-FOR-UPDATE, and others.
You can find the complete list in the documentation about Eligibility of PL/SQL Constructs for Transpilation.
Additional Updates
You could re-formulate the example from above and e.g. use package-level functions and the boolean datatype for SQL. Thanks a lot to my colleague Atif Chaudhry from development for his comments on this. Here are some examples to demonstrate this.
1) With 23ai, one of the limitations is already lifted: Package-level functions can now also be transpiled!
The following simple package example shows the functionality.
CREATE OR REPLACE PACKAGE empposition AS function is_president(p_mgr number) return number; end empposition; / CREATE OR REPLACE PACKAGE BODY empposition AS function is_president(p_mgr number) return number IS begin return (case when p_mgr is null then 1 else 0 end); end; end empposition; /
Now let’s use the package-level function.
SQL> select ename, job from emp where empposition.is_president(mgr)=1; ENAME JOB ---------- --------- KING PRESIDENT
And check the SQL plan.
SQL> select * from dbms_xplan.display_cursor(); PLAN_TABLE_OUTPUT ---------------------------------------------------------------- SQL_ID 6dyw7fyrmxmfu, child number 0 ------------------------------------- select ename, job from emp where empposition.is_president(mgr)=1 Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| EMP | 1 | 18 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(CASE WHEN "MGR" IS NULL THEN 1 ELSE 0 END =1) 18 rows selected.
As you can see it also works with packages.
2) With Oracle database 23, Oracle supports the BOOLEAN data type in SQL. Let’s re-formulate our original example.
create or replace function is_president(p_mgr number) return boolean as begin return (p_mgr is null); end; / SQL> select ename, job from emp where is_president (mgr); ENAME JOB ---------- --------- KING PRESIDENT
With SQL Transpiler enabled, you may get the following result.
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 9qfxb2xjf5chw, child number 1
-------------------------------------
select ename, job from emp where is_president (mgr)
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 18 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("MGR" IS NULL)=TRUE)
18 rows selected.
Summary
SQL Transpiler introduced in Oracle database 23 is a technology to automate the conversion of PL/SQL functions within SQL into SQL expressions. At the moment not all PL/SQL constructs are supported by the SQL Transpiler. However there will be further enhancements. If the transpiler cannot convert a PL/SQL function to SQL, execution of the function falls back to the PL/SQL runtime. Only one parameter needs to be adjusted, no further user intervention is required and the entire operation is transparent to users. So give it a try!
Further Reading