23c 新特性之SQL_transpiler

描述

介绍

有时我们需要查询的选择列表中的函数。这些可能是内置的函数，或者是我们自己编码的函数。默认操作是为查询返回的每一行调用函数。如果函数是确定性的，那么对于相同的输入参数签名，它会给出相同的输出，并且与处理的行数相比，使用的参数签名数较低，这可能会浪费很多资源效率。

Oracle23c 中的自动 SQL_transpiler 允许将一些函数转换为 SQL 表达式，以减少 SQL 中函数调用的开销。我们使用 WHERE 子句中的一个函数作为过滤器来查询表。执行计划中的谓词信息显示该函数被用作筛选器。

的应用

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

测试

测试函数创建

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.

打开前后执行计划对比

不打开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> 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 表达式

执行效率对比

不打开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 表达式。对于高频操作，可极大程度降低函数调用，降低争用，同时还能一定程度上提升性能。