23c 新特性之SQL_transpiler

---

一 描述

1.1 SQL_transpiler介绍

有时我们需要查询的选择列表中的函数。这些可能是内置的函数，或者是我们自己编码的函数。默认操作是为查询返回的每一行调用函数。如果函数是确定性的，那么对于相同的输入参数签名，它会给出相同的输出，并且与处理的行数相比，使用的唯一参数签名数较低，这可能会浪费很多资源效率。
Oracle23c中的自动SQL_transpiler允许将一些函数转换为SQL表达式，以减少SQL中函数调用的开销。我们使用WHERE子句中的一个函数作为过滤器来查询表。执行计划中的谓词信息显示该函数被用作筛选器。

1.2 SQL_transpiler的应用

打开sql_transpiler开关后,Oracle自动将函数转化为SQL表达式。对于高频操作，可极大程度降低函数调用，降低争用，同时还能一定程度上提升性能。

二 SQL_transpiler测试

2.1 测试函数创建

SQL> conn roger/roger@enmopdb1
Connected.
SQL> create table t0710 (
  2    a    number,
  3    b  number,
  c  number
);  4    5  

Table created.
SQL> insert into t0710 values (1, 2, 3), (2, 20, 30), (3, 200, 300);

3 rows created.
SQL> commit;

Commit complete.
SQL> create or replace function add_numbers(p1 in number, p2 in number)
  2  return number as
  3  begin
  4  return p1 + p2;
  5  end;
  6  /

Function created.

2.2 打开前后执行计划对比

不打开SQL_transpiler
SQL> select a, b, c from   t0710 where  add_numbers(b, c) = 500;

         A          B          C
---------- ---------- ----------
         3        200        300


Execution Plan
----------------------------------------------------------
Plan hash value: 925956317

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    39 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T0710 |     1 |    39 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ADD_NUMBERS"("B","C")=500)

打开SQL_transpiler
SQL> alter system flush shared_pool;

System altered.

SQL> alter session set sql_transpiler=on;

Session altered.

SQL> select a, b, c from   t0710 where  add_numbers(b, c) = 500;

         A          B          C
---------- ---------- ----------
         3        200        300


Execution Plan
----------------------------------------------------------
Plan hash value: 925956317

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    39 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T0710 |     1 |    39 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("B"+"C"=500)

此时Oracle自动将函数转化为SQL表达式

2.3 执行效率对比

不打开SQL_transpiler
SQL> alter system flush shared_pool;
System altered.
SQL> set timing on
SQL> declare
  2     c number;
  3     begin
     for i in 1 .  4  . 500000 loop
       select count(1) into c from  t0710 where add_numbers(b, c) = 500;
  5    6       end loop;
  7     end;
  8    /
PL/SQL procedure successfully completed.
Elapsed: 00:00:25.19

打开SQL_transpiler
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.12
SQL> alter session set sql_transpiler=on;

Session altered.

Elapsed: 00:00:00.00
SQL> declare
  2     c number;
  3     begin
  4       for i in 1 .. 500000 loop
  5         select count(1) into c from  t0710 where add_numbers(b, c) = 500;
  6       end loop;
  7     end;
  8    /

PL/SQL procedure successfully completed.

Elapsed: 00:00:16.17

此处测试打开sql_transpiler，执行效率提升大约36%，打开sql_transpiler开关后,Oracle自动将函数转化为SQL表达式。对于高频操作，可极大程度降低函数调用，降低争用，同时还能一定程度上提升性能。