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?

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.

Efficient Function Calls From SQL

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.

PL/SQL Constructs Eligible and not Eligible for Transpilation

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:

Hope this helps. Regards Tim...

Back to the Top.