OracleのSQLプロファイルとSQLプラン・ベースラインの違いと活用方法

本記事では、Oracle®におけるSQLプロファイルとSQLプラン・ベースラインの違いを解説し、クエリチューニング時にそれぞれがどのように機能するのかを詳しく説明します。

オプティマイザ、プロファイル、ベースラインの関係

これら3つの要素は、大まかに以下のように連携して動作します。

• クエリオプティマイザは、システム統計、バインド変数、コンパイル情報などを基に、クエリ実行に最適なプランを導き出します。しかし、入力情報に不備があると、必ずしも最適とは言えないプランを選択してしまうことがあります。

• SQLプロファイルには、こうした問題を緩和するための補助情報が含まれています。これによりオプティマイザの判断ミスを最小限に抑え、最適なプランの選択を支援します。

• SQLステートメントのSQLプラン・ベースラインは、承認済み(accepted)プランの集合で構成されます。ステートメントの解析後、オプティマイザは承認済みプランの中から最適なものを選択します。コストベースのオプティマイザが別の有望なプランを見つけた場合、その新プランはプラン履歴に追加されます。ただし、現行の承認済みプランよりも性能が優れていることが検証されるまで、新プランは実際には使用されません。

イメージとしては次のとおりです。SQLプロファイルは、オプティマイザが最適なプランを選択できるよう情報を提供しますが、特定のプランの選択を強制することはありません。一方、SQLプラン・ベースラインは、オプティマイザのプラン選択を承認済みプランの集合に限定します。コストベースのプランも候補に含めたい場合は、承認済みベースラインのプランセットに組み込む必要があります。

オプティマイザに最小コストのプランを使用させたい場合や、最新の統計情報を活用したい場合はSQLプロファイルを利用します。逆に、特定のプラン群の中から1つを選んで使いたい場合はベースラインが適しています。SQLプラン・ベースラインで承認済みプランの中から最適なものを得られない場合は、SQLプロファイルの使用を検討しましょう。

SQL Plan Management(SPM)

SQL Plan Management(SPM)は、以下の3つのコンポーネントで構成されています。

• プランキャプチャ(Plan Capture)
• プラン選択(Plan Selection)
• プラン進化(Plan Evolution)

SPMプランキャプチャ

ステートメントを実行すると、システムはハードパースを行い、利用可能なSQLプロファイルに基づいてコストプランを生成します。コストベースのプランが選択された後、SQLプラン・ベースライン内の既存プランと比較されます。生成されたコストベースのプランが承認済みプランのいずれかと一致すれば、そのプランを使用できます。一致しない場合は、そのプランは未承認(unaccepted)プランとしてプラン・ベースラインに追加されます。

SPMプラン選択

ベースラインプランを用いてSQLステートメントを実行する際、そのSQLにとって最適なプランが選択されます。オプティマイザも同じプロセスを使用します。また、利用可能なSQLプロファイルは各プランの推定コストに影響を与え、それに応じてプランが選択されます。

SPMプラン進化

SPMの最後のコンポーネントは、未承認プランの進化(Evolution)です。このプロセスでは、未承認プランを承認済みプランと比較検証します。クエリの所要時間や必要なCPUリソースを考慮して最適なプランを評価し、クエリのコストに基づいて最良のプランを承認します。SQLプロファイルが存在する場合は、推定コストに影響を与えます。

プロファイルとベースラインの比較

以下の表は、こちらの資料を参考に、SQLプロファイルとSQLプラン・ベースラインを比較したものです。

アーキテクチャ

次の画像は、SQLプラン・ベースラインのアーキテクチャを示しています。

画像出典:ittutorial.org

SQLプラン・ベースラインのロード

次の画像は、SQLプラン・ベースラインをロードする2つの方法を示しています。

画像出典:ittutorial.org

1つ目の方法は自動プランキャプチャです。初期化パラメータOPTIMIZER_CAPTURE_SQL_PLAN_BASELINESをTRUEに設定して有効化します。このパラメータのデフォルト値はFALSEであるため、以下の例のようにTRUEへ変更してください。

2つ目の方法は手動管理です。DBMS_SPMパッケージを使用して、SQLプラン・ベースラインを手動で管理できます。次の例のように、SQLチューニングセット(SQL Tuning Set)からプランをロードします。

SQLプラン・ベースラインの手動ロード

次のコマンドを使用すると、プラン・ベースラインを手動でロードできます。

SQLプラン・ベースラインの使用状況の確認

SQLプラン・ベースラインをロードした後は、SQLを実行してオプティマイザがベースラインを実際に使用しているかどうかを確認する必要があります。SQL_TEXTとプラン名を指定して、次のようにSQLプラン・ベースラインを照会できます。

SQLプラン・ベースラインの表示

次のクエリを実行すると、SQLプラン・ベースラインの一覧を表示できます。

SQLプラン・ベースラインの削除

SQLプラン・ベースラインを削除するには、まず次のクエリを実行して、オプティマイザが現在使用しているSQLプランを確認します。

使用中のプランを特定した後、次のコマンドを実行してベースラインを削除します。

Oracle SQLプロファイル

SQLチューニングアドバイザー(SQL Tuning Advisor)は、Oracle Enterprise Manager(OEM)から起動するか、コマンドラインのクエリで実行でき、SQLステートメントに対するSQLプロファイルを生成できます。このプロファイルには、当該ステートメントに関する追加情報が含まれます。

実行例

ここでは、まず対象のsql_idに対してSQLチューニングアドバイザーを実行し、その後SQLプロファイルに対する各種操作を行います。

1. SQLチューニングアドバイザーの実行

sql_id「6dkrnbx1zdwy38」に対して、次のSQLチューニングアドバイザーのコードを実行します。

推奨事項を確認するため、次のDBMS_SQLTUNE.report_tuning_taskを実行します。

2. SQLプロファイルの受け入れ(Accept)

次のコードを実行して、SQLプロファイルを受け入れます。

3. SQLプロファイル名の確認

次のクエリを使用して、SQLプロファイルの名前を確認します。

4. SQLプロファイルの無効化

次のコードを実行して、SQLプロファイルを無効化します。

再度有効化する場合は、ステータスの値をDISABLEDからENABLEDに変更します。

5. SQLプロファイルの削除

次のコードを実行して、SQLプロファイルを削除します。

まとめ

SQLステートメントを実行すると、オプティマイザは実行プランを作成し、クエリを解析してハードディスクからデータを取得し、メモリ上に展開します。SQLプロファイルとSQLプラン・ベースラインは、時間およびCPUコストの観点から最も低コストのプランをオプティマイザが選択できるよう導きます。優れたSQLプランは、クエリを効率的に実行し、目的の結果をより高速に提供します。

データベースサービスの詳細については、ぜひご相談ください。

ご意見やご質問がある場合は、フィードバックタブからお気軽にお寄せください。また、私たちとの対話を始めることもできます。