8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Automatic PL/SQL to SQL Transpiler in Oracle Database 23c
The automatic SQL transpiler in Oracle 23c allows some functions to be converted into SQL expressions to reduce the overhead of function calls in SQL.
- Setup
- The Problem
- Automatic SQL Transpiler
- Query Transformation
- What can be Transpiled?
- Packaged Functions?
Related articles.
Setup
The examples in this article require the following objects.
We create a table and populate it with three rows.
drop table if exists t1; create table t1 ( id number, val1 number, val2 number ); insert into t1 (id, val1, val2) values (1, 1, 2), (2, 10, 20), (3, 100, 200); commit;
We create a function to add two values together.
create or replace function add_numbers(p1 in number, p2 in number) return number as begin return p1 + p2; end; /
The Problem
Functions allow us to write modular code, but calling functions from SQL adds an overhead because of context switching between the SQL and PL/SQL engines. There are several existing ways to reduce the impact of calling functions from SQL described here.
Automatic SQL Transpiler
The automatic SQL transpiler in Oracle 23c allows some functions to be converted into SQL expressions to reduce the overhead of function calls in SQL.
We query the table using a function in the WHERE
clause as a filter. The predicate information with the execution plan shows the function was used as a filter.
select id, val1, val2 from t1 where add_numbers(val1, val2) = 300; select * from dbms_xplan.display_cursor(); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID dcttgua9qy5x4, child number 0 ------------------------------------- select id, val1, val2 from t1 where add_numbers(val1, val2) = 300 Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 1 | 39 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ADD_NUMBERS"("VAL1","VAL2")=300) Note ----- - dynamic statistics used: dynamic sampling (level=2) 22 rows selected. SQL>
We connect to a privileged user, flush the shared pool and connect back to the test user.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba alter system flush shared_pool; conn testuser1/testuser1@//localhost:1521/freepdb1
We turn on the automatic SQL transpiler for our session by setting the SQL_TRANSPILER
initialization parameter, then repeat the test.
alter session set sql_transpiler = 'ON'; select id, val1, val2 from t1 where add_numbers(val1, val2) = 300; select * from dbms_xplan.display_cursor(); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID dcttgua9qy5x4, child number 0 ------------------------------------- select id, val1, val2 from t1 where add_numbers(val1, val2) = 300 Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 1 | 39 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("VAL1"+"VAL2"=300) Note ----- - dynamic statistics used: dynamic sampling (level=2) 22 rows selected. SQL>
Notice the filter no longer calls the function. The function has been transpiled directly into the SQL.
Query Transformation
Let's see what happens behind the scenes when we use the automatic SQL transpiler.
First we flush the shared pool and identify the trace file that will be created for our new session.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba alter system flush shared_pool; conn testuser1/testuser1@//localhost:1521/freepdb1 set linesize 100 column value format a65 select value from v$diag_info where name = 'Default Trace File'; VALUE ----------------------------------------------------------------- /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_16528.trc SQL>
Now we do a 10053 trace of the statement.
alter session set sql_transpiler = 'ON'; alter session set events '10053 trace name context forever'; select id, val1, val2 from t1 where add_numbers(val1, val2) = 300; alter session set events '10053 trace name context off';
We check the resulting trace file, searching for the section beginning with "Final query after transformations", and we see the following statement.
Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "T1"."ID" "ID","T1"."VAL1" "VAL1","T1"."VAL2" "VAL2" FROM "TESTUSER1"."T1" "T1" WHERE "T1"."VAL1"+"T1"."VAL2"=300
The statement has been transformed to remove the function and replace it with the direct calculation.
What can be Transpiled?
The documentation contains a list of items can and can't be used with this functionality.
Packaged Functions?
In the current release, even a simple packaged function can't be transpiled. We can show this by repeating the previous test with a simple packaged function.
We create a package containing the function we used previously.
create or replace package test_pkg as function add_numbers(p1 in number, p2 in number) return number; end; / create or replace package body test_pkg as function add_numbers(p1 in number, p2 in number) return number as begin return p1 + p2; end; end; /
We connect to a privileged user, flush the shared pool and connect back to the test user.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba alter system flush shared_pool; conn testuser1/testuser1@//localhost:1521/freepdb1
We turn on the automatic SQL transpiler for our session by setting the SQL_TRANSPILER
initialization parameter, then perform the test using the packaged function.
alter session set sql_transpiler = 'ON'; select id, val1, val2 from t1 where test_pkg.add_numbers(val1, val2) = 300; select * from dbms_xplan.display_cursor(); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 6pjq0tbfc43qb, child number 0 ------------------------------------- select id, val1, val2 from t1 where test_pkg.add_numbers(val1, val2) = 300 Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 1 | 39 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TEST_PKG"."ADD_NUMBERS"("VAL1","VAL2")=300) Note ----- - dynamic statistics used: dynamic sampling (level=2) 23 rows selected. SQL>
Notice the packaged function is still present in the execution plan.
For more information see:
- Automatic SQL Transpiler
- PL/SQL Constructs Eligible and not Eligible for Transpilation
- Efficient Function Calls From SQL
Hope this helps. Regards Tim...