皆さん、明けましておめでとうございます。今年の年末年始は暖かくて過ごしやすい気候でしたが、初詣には行かれましたでしょうか。私は毎年近くの決まったところに行くようにしています。今年も素敵な一年になると良いですね。
今年最初の内容は、第38回のSQLチューニングの続きとして、アプリケーションを変更できないときでもSQLを変更するのに使用できる、Oracle Database 12c（12.1.0.1）からのSQL翻訳フレームワークについて説明しますので、参考にしてください。
1. SQL翻訳フレームワーク（SQL Translation Framework）
第38回で説明したように、パッケージ・アプリケーションなどの簡単にSQLを変更できない場合に、SQLチューニングではSPMのSQL計画ベースラインを使用しますが、ヒント・レベルでのチューニングだけしか対応することができません。また、SQLに対して実行計画を固定化してしまうので、バインド変数を使用したSQLでは最適でない場合があり、すべてで使用できるとは限りませんでした。そのため、自動的にSQLを置き換えるのが必要なこともあるので、これを実現するOracle Database 12c（12.1.0.1）からのSQL翻訳フレームワークについて説明します。
SQL翻訳フレームワークは、SQLの置き換え内容をSQL翻訳プロファイルに設定することで、実行時にSQLを置き換えて実行することができるようになります。基本は、Oracleデータベース以外から移行するときに、実行できないSQLを置き換えることで、移行コストを削減するために使用しますが、SQLチューニングでSQLを書き換える必要があるときなどにも使用することができます。例えば、SQL自動変換（問合せトランスフォーマ）でも最適なSQLに変更できないときなどに、プログラムを修正せずにSQLを変更することが可能になります。また、バージョンアップしたときに、新しい最適なSQLに変更するような使い方も可能です。
このSQL翻訳フレームワークは、以下の二つのコンポーネントから構成されていて、これの設定によって様々なSQLの置き換えができるようになっています。
- SQLトランスレータ
これが実際のSQLを置き換える内容になり、PL/SQLパッケージ内のプロシージャで行う方法（トランスレータ・パッケージ）と翻訳するSQLやエラーを直接指定する方法（カスタム翻訳）があります。これをSQL翻訳プロファイルに登録して使用しますが、両方指定することも片方だけ指定することも可能です。また、同じものを複数のSQL翻訳プロファイルで使用することもできます。 - SQL翻訳プロファイル
これがSQL翻訳を指定する単位になるデータベース・オブジェクトです。これに対して一つのトランスレータ・パッケージといくつかのカスタム翻訳を登録して使用します。SQL文を翻訳するときに、まず登録されたカスタム翻訳の有効なものを検索し、一致するものが見つからなかった場合にのみトランスレータ・パッケージを起動するようになるので、二つを効果的に使用することができます（カスタム翻訳は、トランスレータ・パッケージを上書きするようにも使用できます）。
それでは、SQL翻訳プロファイル、カスタム翻訳、トランスレータ・パッケージのそれぞれについてもう少し説明していきます。
（１）SQL翻訳プロファイル
まずは、SQL翻訳プロファイルについて説明します。
SQL翻訳フレームワークを使用するには、SQL翻訳プロファイルをデータベース・オブジェクトとして作成する必要があります。SQL翻訳プロファイルは、以下のようにdbms_sql_translator.create_profileプロシージャを使用して作成します（これを行うには’create sql translation profile’権限が必要です）。このSQL翻訳プロファイルは、データベース・オブジェクトとしてビュー’user_objects’などにも登録されます。
このSQL翻訳プロファイルを使用するには、データベース・サービス・レベル（dbms_serviceパッケージやsrvctlコマンド）またはセッション・レベル（ALTER SESSION文）で、アクティブにすることで可能になっています。セッション・レベルでは、以下のようにSQL翻訳プロファイルを設定します（これを実行するには’alter session’権限が必要です）。
あるユーザのときだけ常に有効にしたい場合には、以下のようなLOGONトリガーを使用すると良いでしょう。
作成したSQL翻訳プロファイルの確認は、以下のようにビュー’user_sql_translation_profiles’を使用して行うことが可能です（列’TRANSLATOR’は、トランスレータ・パッケージを登録すると出力されます）。
このように、用途やユーザごとに作成することができるようになっています。
（２）カスタム翻訳
次に、カスタム翻訳について説明します。
カスタム翻訳は、翻訳するSQLをSQL翻訳プロファイルに直接登録することで、特定のSQLを置き換えることができるようになります。そのため、パッケージ・アプリケーションのように簡単にSQLを変更できないときでも、一時的に特定のSQLを実行時に置き換えることも可能になるので、SQLチューニングではこれを使用するのが多いと思います。
ここからは、このカスタム翻訳の登録と実行について、いくつかの簡単な例を使用して説明していきます。
一つ目の例は、’select * from tab1’を’select * from tab2’に置き換えるように、カスタム翻訳をSQL翻訳プロファイル’TEST_PROFILE1’に登録します。カスタムSQL翻訳は、以下のようにdbms_sql_translator.register_sql_translationプロシージャを使用して登録します（sql_textで翻訳するSQL、translated_sqlで翻訳後のSQLを指定します）。Oracleデータベース以外からの置き換えでは、エラーの翻訳が必要なときにdbms_sql_translator.register_error_translationプロシージャも使用します。
それでは、このカスタム翻訳がどのようになるか、実際に実行して確認してみます。 カスタム翻訳で登録したSQLを実行してみると、以下のようにSQLが置き換わって実行されました。このように簡単に置き換えることができますが、実行計画を見るなどしないと確認できないので注意してください。
カスタムSQL翻訳の確認は、以下のようにビュー’user_sql_translations’を使用して行うことが可能です（カスタム・エラー翻訳は、ビュー’user_error_translations’で確認できますが、ここでは使用しないので省略します）。
このビューでの注意点は、SQL_IDが実際に実行されるSQLではないので（SQL_TEXTのものなので）、以下のように動的パフォーマンス・ビュー’V$SQL’などに登録されるものと異なります（先程の実行計画のSQL_IDからも分かると思います）。そのため、AWRなどからSQLを探すときには、使用できないので注意してください。
二つ目の例は、バインド変数を使用したSQLで確認します。以下のように、’select * from tab1 where c1 = :abc’を’select * from tab2 where c1 = :abc’に置き換えるカスタム翻訳を、SQL翻訳プロファイル’TEST_PROFILE1’に登録して実行してみました。
これも以下のように問題なく翻訳されているので、特にSQLに制限などはないようです。
それから、SQL翻訳プロファイルを無効にする方法についても載せておきます。以下のようにALTER SESSION文を行うことで可能です（これも実行計画を見ると翻訳されていないことが分かります）。
このように、簡単に登録することができるので、一時的に置き換えるときには便利だと思いますが、数が多いと管理が大変になるので注意してください。
（３）トランスレータ・パッケージ
最後に、トランスレータ・パッケージについて説明します。
PL/SQLパッケージ（TRANSLATE_SQLプロシージャとTRANSLATE_ERRORプロシージャ）を作成することで、独自のSQLトランスレータを作成することができます。これは、直接SQLを指定しないようなときや翻訳するSQLの数が多いときに使用します。そのため、アプリケーション全体に対するトランスレータを作成するときはこれを使用する方が良いです。このトランスレータ・パッケージは、SQL ServerやSybase ASEなどの移行用のものがOracleデータベースから提供されています（使用方法については、Oracle Database 11gR2 移行ガイド「Oracle SQL DeveloperによるSQL翻訳フレームワークのインストールと構成」を参照してください）。
このトランスレータ・パッケージについても簡単な例を使用して使い方について説明していきます。
ここでは、スキーマ’testuser’のパッケージ’test_translator’をSQL翻訳プロファイル’TEST_PROFILE2’に登録します。トランスレータ・パッケージは、以下のようにdbms_sql_translator.set_attributeプロシージャを使用して登録します（attribute_nameに’DBMS_SQL_TRANSLATOR.ATTR_TRANSLATOR’、attribute_valueにパッケージ名を指定します）。これによって、パッケージ名がビュー’user_sql_translation_profiles’の列’TRANSLATOR’に設定されます。
以下のような簡単なサンプル・パッケージを作成して確認してみます（SQLは’tab1’を’tab2’に変換して、エラーは何もしないプロシージャになります）。
実行方法は、カスタム翻訳と変わりません。また、以下のようにdbms_sql_translator.translate_sqlプロシージャを使用することで、簡単にSQL翻訳の確認を行うことができます（表名が’tab1’から’tab2’に置き換わっています）。これは置き換えたときだけ出力されます。
このように、そんなに難しい訳ではないので、機会があれば使用してみてください。ただし、有効にするとすべてのSQLで実行されるので注意してください。
2. おわりに
今回はSQL翻訳フレームワークについて説明しましたが、少しは参考になりましたでしょうか。今年も頑張りますのでよろしくお願いします。質問をお待ちしています（このような内容を取り上げて欲しいというご要望などもお待ちしております）。
それでは、次回まで、ごきげんよう。