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 表示式。對於高頻操作，可極大程度降低函式呼叫，降低爭用，同時還能一定程度上提升效能。