Oracle Database 12cに搭載された「SQL翻訳フレームワーク（SQL Translation Framework）」を試します。
SQL翻訳フレームワークは、Oracle Databaseに投入されたSQL文と、実際に実行されるSQL文の変換を行うプログラムです。この機能を利用するために、DBMS_SQL_TRANSLATORパッケージを利用します。SQL文が変更できないパッケージ製品発行するSQL文をチューニング目的で変更したり、ヒントを強制的に付与する等の用途が考えられます。
SQL翻訳フレームワークを利用するためには、SQL Translation Profileと呼ばれるオブジェクトを作成し、このオブジェクトに変更前と変更後のSQL文のセットを複数登録します。利用者はSQL Translation Profileを自セッションに指定して任意のSQL文を実行します。
SQL Translation Profileオブジェクトの作成
SQL Translation Profileはスキーマ・オブジェクトです。CREATE文は提供されておらず、DBMS_SQL_TRANSLATOR.CREATE_PROFILEプロシージャを実行します。作成にはCREATE SQL TRANSLATION PROFILEシステム権限が必要です。
SQL> EXEC DBMS_SQL_TRANSLATOR.CREATE_PROFILE('TEST_PROFILE1') ;
PL/SQL procedure successfully completed.
SQL文の登録
SQL Translation Profileに変更前のSQLと変更後のSQLのセットを登録します。SQL文をそのままDBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATIONプロシージャに渡します。この際、第一パラメーターにはSQL Translation Profileのオブジェクト名を指定します。
第4パラメーターは省略可能です。SQL文の変更を無効にする場合にはFalseを指定します。下記の例では「SELECT * FROM emp」というSQL文が「SELECT * FROM dept」というSQL文に変換されることを示しています。
この時点では、SQL文は解析されず、EMPテーブルやDEPTテーブルがどのユーザーの所有かは確定されていません。
SQL> EXEC DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION('TEST_PROFILE1', 'SELECT * FROM emp', 'SELECT * FROM dept') ;
PL/SQL procedure successfully completed.
実行権限の付与
SQL Translator Profileを他のユーザーに利用させるには、ユーザーがTRANSLATE ANY SQLシステム権限を持つか、オブジェクト権限が必要です。
SQL> GRANT TRANSLATE ANY SQL TO SCOTT ;
Grant succeeded.
SQL Translation Profileの利用
SQL Translation Profileを利用するには、初期化パラメーターsql_translation_profileにSQL Translator Profileオブジェクトの名前を指定します。インスタンス単位以外にセッション単位でも指定できます。他ユーザーが作成したプロファイルを使用する場合には、ユーザー名も含まて指定します。またイベントの設定も必要です。
$ sqlplus SCOTT/password
SQL> ALTER SESSION SET sql_translation_profile = SYS.TEST_PROFILE1 ;
Session altered.
SQL> ALTER SESSION SET EVENTS = '10601 trace name context forever, level 32';
Session altered.
変換の検証
変換が実行されることを確認
実際にSQL文を実行して変換されているか確認します。実行計画を確認するとEMPテーブルを検索しているはずが、DEPTテーブルを検索していることがわかります。
SQL> SELECT * FROM emp;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR()) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID c8fpf7w1j3x79, child number 2
-------------------------------------
SELECT * FROM dept
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| DEPT | 1 | 11 | 2 (0)| 00:00:01 |
10 rows selected.
SQL>
実行されないことを確認
一方で、登録したSQL文とほんの僅かでも異なるSQL文を実行した場合は意味が同じでも変換は行われません。これは共有プールに実行計画がキャッシュされるときと同じ動作です。
SQL> SELECT * FROM emp ; ← 行末にスペースが付いている。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR()) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7bn5hjhdvy77r, child number 1
-------------------------------------
SELECT * FROM emp
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 00:00:01 |
10 rows selected.
SQL>
バグ？
SQL*PlusのAUTOTRACE機能を使うとSQL Translation Frameworkで変換される前の実行計画が表示されます。
SQL> SELECT * FROM emp;
DEPT DEPT
---- ----------
100 dept1
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
617 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL Translation Frameworkは面白い機能ですが、SQL文の比較をバイト単位で行っている部分が使い方が難しいところです。これ以外にも、Oracle Database 12cの新機能はには入力したSQL文と、実行されるSQL文が異なる場合が多くあります。実行計画を確認してみると内部構造が分かって面白いと思います。