DBA Hub

📋Steps in this guide1/6

Automatic PL/SQL to SQL Transpiler in Oracle Database 23ai/26ai

The automatic SQL transpiler in Oracle 23ai/26ai allows some functions to be converted into SQL expressions to reduce the overhead of function calls in SQL.

oracle 23configurationintermediate
by OracleDba
14 views
1

Setup

The examples in this article require the following objects. We create a table and populate it with three rows. We create a function to add two values together.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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;

create or replace function add_numbers(p1 in number, p2 in number)
return number as
begin
  return p1 + p2;
end;
/
2

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
3

Automatic SQL Transpiler

The automatic SQL transpiler in Oracle 23ai/26ai 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 clause as a filter. The predicate information with the execution plan shows the function was used as a filter. We connect to a privileged user, flush the shared pool and connect back to the test user. We turn on the automatic SQL transpiler for our session by setting the initialization parameter, then repeat the test. Notice the filter no longer calls the function. The function has been transpiled directly into the SQL.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
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>

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba
alter system flush shared_pool;
conn testuser1/testuser1@//localhost:1521/freepdb1

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>
4

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. Now we do a 10053 trace of the statement. We check the resulting trace file, searching for the section beginning with "Final query after transformations", and we see the following statement. The statement has been transformed to remove the function and replace it with the direct calculation.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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>

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';

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
5

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
6

Packaged Functions?

In the early releases, even a simple packaged function could not be transpiled. We can show this is no longer the case by repeating the previous test with a simple packaged function. We create a package containing the function we used previously. We connect to a privileged user, flush the shared pool and connect back to the test user. We turn on the automatic SQL transpiler for our session by setting the initialization parameter, then perform the test using the packaged function. Notice the packaged function is not referenced in the execution plan. The early versions of 23ai/26ai showed the predicate line like this. 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...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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;
/

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba
alter system flush shared_pool;
conn testuser1/testuser1@//localhost:1521/freepdb1

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("VAL1"+"VAL2"=300)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


23 rows selected.

SQL>

1 - filter("TEST_PKG"."ADD_NUMBERS"("VAL1","VAL2")=300)

Comments (0)

Please to add comments

No comments yet. Be the first to comment!