※ 本記事は、Ulrike Schwinnによる”SQL Transpiler in 23c – automatic PL/SQL conversion into SQL“を翻訳したものです。

2023年8月18日

特にPL/SQL、SQL、およびパフォーマンスに関する23cの機能を参照しているときに、SQLトランスパイラと呼ばれる非常に興味深い新機能を見つけました。その機能の名前で、もっと知りたいと思いました。その結果、その情報をまとめて、簡単な例で機能と設定について説明します。

この機能は、SQL問合せでコールおよび使用されるストアドPL/SQLファンクションに対して行われます。ご存知のとおり、これらのユース・ケースではオーバーヘッドが発生する可能性があります。SQLの実行時にPL/SQLランタイムを起動する必要があるため、SQLエンジンとPL/SQLエンジンの間でのコンテキスト切替えが発生します。

例を挙げましょう。IS_PRESIDENTという次のPL/SQLファンクションは、表EMPの従業員にジョブ・ロールPRESIDENTがあるかどうかを検出します。そのためには、MGR列の値のみを確認する必要があります。値が1の場合、従業員はジョブ・ロールPRESIDENTを持ちます。

create or replace function is_president(p_mgr number) 
return number
as
begin
   return (case when p_mgr is null then 1 else 0 end);
end;
/

問合せのWHERE句で使用します。ename KINGを持つ従業員がジョブ・ロールPRESIDENTを持つことがわかります。

SQL> select ename, job from emp where is_president (mgr) = 1;

ENAME      JOB
---------- ---------
KING       PRESIDENT

述語情報を含む実行計画について見てみましょう。DBMS_XPLAN.DISPLAY_CURSORを使用して、カーソル・キャッシュにロードされたカーソルの実行計画を表示します。

SQL> SET LINESIZE 130 PAGESIZE 0

SQL> select * from dbms_xplan.display_cursor();
SQL_ID  0n924y0tk6mrt, child number 0
-------------------------------------
select ename, job from emp where is_president (mgr) = 1

Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    18 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("IS_PRESIDENT"("MGR")=1)

18 rows selected.

述語情報セクションには、PL/SQLファンクションIS_PRESIDENTの呼出しがあります。SQLとPL/SQLにはコンテキスト・スイッチがあります。

これを回避するにはどうすればよいでしょうか。21cに精通している場合は、SQLマクロ・テクノロジでこれを解決できます。この状況を回避するために、適切なSQLマクロ関数を記述できます。この例では、次のようになります。

create function my_is_president (p_mgr number) 
return varchar2 SQL_MACRO(SCALAR) 
as
begin
  return('case when p_mgr is null then 1 else 0 end');
end;
/  

SQLマクロに関心がある場合は、Database PL/SQL言語リファレンスを参照してください。

もちろん、23cでも同じ技術を使用できます。ただし、23cでは、Oracleはさらに1ステップ進み、SQLトランスパイラを使用した自動変換を導入しています。新しいパラメータSQL_TRANSPILERを使用して、トランスパイラ機能を設定して有効にするのみです。これ以上の手動の操作は必要ありません。SQLトランスパイラは、デフォルトで無効になっています。ALTER SYSTEMまたはALTER SESSIONコマンドを使用して有効にし、パラメータ値を適宜変更できます。SQL_TRANSPILERをONに設定すると、SQLトランスパイラ機能が有効になり、PL/SQLファンクションは可能なかぎり自動的にSQL式にトランスパイル(変換)されます。このパラメータをOFFに設定すると、SQLトランスパイラ機能は無効になります。

まず、パラメータ値を確認します。

SQL> sho parameter sql_transpiler

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sql_transpiler                       string      OFF

パラメータを変更して、SQLトランスパイラを有効にします。パラメータの詳細は、データベース・リファレンスを参照してください。

SQL> alter session set sql_transpiler=on;
Session altered.

次に、問合せを再度実行します。

SQL> select ename, job from emp where is_president(mgr) =1; 

ENAME      JOB
---------- ---------
KING       PRESIDENT

前述のとおり、DBMS_XPLAN.DISPLAY_CURSORを使用して実行の詳細を確認します。

SQL> select * from  dbms_xplan.display_cursor ();

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fn9sdk4fca4yh, child number 0
-------------------------------------
select ename, job from emp where is_president (mgr) =1

Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    18 |     3   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(CASE  WHEN "MGR" IS NULL THEN 1 ELSE 0 END =1)

18 rows selected.

「Predicate Information」セクションに表示されるように、PL/SQLファンクション・コールIS_PRESIDENT (MGR)は「CASE  WHEN “MGR” IS NULL THEN 1 ELSE 0 END」に置き換えられます。これは、トランスパイルが発生したことを示します。これは、トランスパイルが発生したことを示します。

注意: このリリースのトランスパイラでは、すべてのPL/SQL構文がサポートされるわけではありません。さらなる機能強化と制限が解除されます。たとえば、PLS_INTEGER、%TYPE、/%ROWTYPE変数、PL/SQL集計タイプまたはオブジェクト・タイプなどのPL/SQL要素は、現時点ではトランスパイルに適していません。
その他の制限事項は次のとおりです。:

  • 埋込みSQL文。トランスパイルされた関数には、カーソル宣言、明示的なカーソル、参照カーソルまたは実行即時文を含めることはできません
  • PL/SQLパッケージ内で定義されたファンクション。
  • publicおよびprivateの両方のパッケージ変数。
  • ローカルに定義された(ネストされた)関数
  • COMMIT、ROLLBACK、LOCK-TABLE、PRAGMA AUTONOMOUS TRANSACTION、SELECT-FOR-UPDATEなどのトランザクション処理

PL/SQLコンストラクトのトランスパイルの適格性に関するドキュメントの完全なリストを参照できます。

まとめ

23cで導入されたSQLトランスパイラは、SQL内のPL/SQLファンクションからSQL式への変換を自動化するテクノロジです。現時点では、すべてのPL/SQL構造がSQLトランスパイラでサポートされているわけではありません。ただし、さらに機能強化が行われます。トランスパイラがPL/SQLファンクションをSQLに変換できない場合、ファンクションの実行はPL/SQLランタイムにフォールバックします。調整が必要なパラメータは1つのみで、これ以上のユーザーの介入は不要で、操作全体がユーザーに対して透過的です。試してみてください!

さらに読む