津島博士のパフォーマンス講座　第57回　適応問合せ最適化について

津島博士のパフォーマンス講座　Indexページ ▶▶

皆さん、明けましておめでとうございます。今年は暖冬かと思っていたら、成人の日頃からやっと冬らしい寒さになってきましたが、今年も素敵な一年になると良いですね。
今年最初の内容は、Oracle Database 12c（Oracle12c）でSQLの実行が遅くなったと聞くことがありましたので、影響している場合が多い適応問合せ最適化（Adaptive Query Optimization）を取り上げようと思います。これによってオプティマイザ統計の補正が強化されていますので、上手く使用すると便利な機能ですが、何も知らないと問題になる場合があります。そのため今回は、第33回の続きとして適応問合せ最適化を利用するためのガイドについて説明しますので、参考にしてください。

1. 適応問合せ最適化の問題点

まずは、適応問合せ最適化の問題点について説明します。
適応問合せ最適化には、適応計画（結合方法、パラレル分散方法）と適応統計（自動再最適化、動的統計、SQL計画ディレクティブ）があり、問題になるのは適応統計になります。
適応統計は、オプティマイザ統計が不十分なときに補正する便利な機能ですが、動的統計が動作するとパース時間が長くなり、実行時間が短いなどの影響するSQLは注意が必要です。通常は、静的統計収集（DBMS_STATS）をすると、SQL計画ディレクティブ（SPD:SQL Plan Directive）の状態が’Has_Stats’になり、補正する必要がなくなるので、テスト時にある程度実行しておくとそんなに気にする必要はありません。ただし、すべての統計を改善できる訳ではないので、状態が’Permanent’（SPDだけが改善できる）になるのが問題になります（以下のように、拡張統計を作成できないか、作成しても正しく見積もれないと、永続的にSPDから動的統計が動作します）。

以下のような列にSPDが作成されたときに、既存の統計情報で正しく見積もれないと状態が’Permanent’になります。これはオプティマイザ統計の限界を、動的統計が解決しているという問題のないことですが、オーバーヘッドが大きいと性能問題になります（そのときには、後で説明する利用ガイドを参考にしてください）。

• 式/関数の条件（拡張統計の式統計は、SPDから自動的に作成されない）
• 結合や複数表のGROUP BY（複数表の列で求める統計情報は格納できない）
• 列データ長が大きいやユニーク値が多い（ヒストグラムの最大値を超えている）

それでは、適応統計のそれぞれについてもう少し説明します。

（１）自動再最適化（統計フィードバック）
統計フィードバックは、見積りと実行後の実際のカーディナリティ（行数）が大きく異なるときに、実際のカーディナリティをメモリ上に格納することで、次回実行時にその統計を使用して、最適な実行計画を作成する機能です。ただし、他のSQLに共有できないので、同じようなSQLでも最初は非効率な実行計画で動作していましたが、SPDを作成することで改善しています。これにより、初回実行時（またはカーソルがメモリからなくなったとき）に利用可能なSPDが存在すると、動的統計を使用してオプティマイザ統計を補正することが可能になります。また、これまで単一表のカーディナリティだけしか補正できなかったものが、結合やGROUP BYのカーディナリティも追加されているので、より最適な実行計画になるSQLが増えています。この機能自体は、オーバーヘッドもなく問題になりませんが、SPDが作成されて動的統計を起動することが注意点になります。

（２）動的統計（OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータ）
動的統計は、SQL実行時に表をサンプル・スキャンするので、できるだけ動作させないように、オプティマイザ統計を収集していると、デフォルトでは動作しません。ただし、拡張統計については、作成していないことが多いので（自動作成されないので）、Oracle11gR2からパラレル実行のときだけ、WHERE句の条件が列グループや式/関数に対して、デフォルトでも動作するようにしています（表の大きさも含めて最適なサンプル・レベルを自動決定します）。これがOracle12cからは、以下のような適応動的統計（または自動動的統計）が追加されて、使用しやすくなり、動作するものが増えています。

• パラメータ値が11のとき（適応動的統計が動作する）
  これまで意識して設定する必要があったのが、条件の複雑さ（結合やGROUP BYも含む）、既存の基本統計（拡張統計やヒストグラムの存在など）、SQLの総実行時間に基づいて、オプティマイザが自動的に判断して動作します。ただし、存在している基本統計で問題ないときでも動作してしまうのが注意点です。
• パラメータ値がデフォルトでパラレル実行のとき
  パラメータ値を11に設定して、上記の適応動的統計が動作します。
• SPDが存在するとき
  パラメータ値が0以外のときに適応動的統計が動作します。

適応動的統計は、結果キャッシュ（Result Cache）を使用して、他のSQLでも動的統計を共有できるようになっています（動的統計の再帰的SQLは、以下のようにRESULT_CACHEヒントを使用しているので、同じSQLだと結果キャッシュが共有されます）。ただし、数が多いとデフォルト・サイズではラッチ競合しやすいなどから、3600秒までしか再利用できなくなっています（これについては、Oracle12cR2で改善される予定です）。

（３）SQL計画ディレクティブ（SPD）
SPDは、見積りが大きく異なるときに、他のSQLなども補正できるように作成します。そのため、オプティマイザ統計が正確でないと動作する機会が多くなるので、オプティマイザ統計を更新しないで実行計画を固定化するときなどでは動作しないようにしてください。
SPDには、動的統計の起動と列グループ統計の自動作成という二つの機能があります。動的統計のオーバーヘッドが大きいときは、列グループ統計を自動的に作成するだけでも有効な場合もあるので、これだけを使用することも検討してください。

2. 適応問合せ最適化の利用ガイド

次に、適応問合せ最適化（特に適応統計）を利用するガイドについて説明します。
適応統計は、使用すると必ず問題になる訳ではないので、OLTPのような実行時間の短いSQLや実行計画を固定化するようなとき以外は使用すると効果があると思います。ただし、動的統計によって、パース時間が長い（第32回で説明した共有カーソルの待機イベント時間が多い）場合、以下のことを検討するようにしてください。このとき最初の静的統計収集で改善できれば良いのですが、そうでなければ動作しないように、それ以降を実施していきます。これにより最適な実行計画にならない場合もあるので、そのときはヒントなどでSQLチューニングを行うことになります。

• 静的統計収集を行う
• SPDを使用しない
• 特定のSQLの動的統計を行わない
• 特定のSPDをDISABLEにする

それでは、それぞれについて説明していきます。

（１）静的統計収集を行う
まずは、静的統計収集を行って、オプティマイザ統計を正確にします。
これでSPDの状態が’Has_Stats’になると、動的統計が動作しなくなります。ただし、既存のオプティマイザ統計で改善できないと、SPDの状態が’Permanent’になり、永続的に動的統計が動作することになります。このとき不足している統計を、手動で作成することで改善できる場合がります。例えば、式の統計（拡張統計）は、SPDから自動作成されないので、WHERE句に式/関数を使用しているときに、手動で作成することで動的統計が動作しないようにできます。また、オプティマイザ統計の限界で、動的統計を止めることができないものもあることを知っておいてください。例えば、複数表のカーディナリティ（結合やGROUP BY）は、オプティマイザ統計に情報がないので、手動でも作成できません。
パラレル実行時の動的統計についても、統計（拡張統計やヒストグラムなど）を追加することで動作しなくなる場合があります。ただし、改善できないときには、必要ないパラレル実行（実行時間が短いなど）を行わないようにしてください。

（２）SPDを使用しない
SPDが問題のときは、SPDだけを使用しないようにはできないので（適応統計だけもできないので）、適応問合せ最適化機能のすべてを使用しないようにする必要があります（OPTIMIZER_ADAPTIVE_FEATURES初期化パラメータをFALSEにします）。性能向上しているSQLがあり、システム全体で設定できない場合、このパラメータをALTER SESSION文で設定するか、他の方法を検討する必要があります。ただし、SPDを使用するときには、改善するためのいくつかのパッチが提供されているので、適用を検討するようにしてください。

（３）特定のSQLの動的統計を行わない
適応問合せ最適化機能は使用したいが、特定のSQLだけパース時間が長くなり、実行時間が遅くなってしまう場合、そのSQLだけ動的統計を動作しないようにします。具体的には、以下のようにDYNAMIC_SAMPLINGヒントに0を設定することで、動的統計が動作しなくなります。ヒントが使用できる場合は、これが個別に無効化する最も簡単な方法になります。パラレル実行の動的統計は、この指定でないと動作しないようにできません。

SQL> SELECT /*+ DYNAMIC_SAMPLING(0) */ … ;
SQL> SELECT /*+ DYNAMIC_SAMPLING(<表名> 0) */ … ;

SPDによる列グループ統計の自動作成は使用したいが、オーバーヘッドの大きい動的統計はすべて動作させたくないのであれば、OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータを0にするのが良いです。これでSPDを列グループ統計の作成だけで使用することができます。

（４）特定のSPDをDISABLEにする
動的SQLなどによりヒントを指定できないときは、問題になるSPDだけを個別にDISABLEにすることで、動的統計を動作しないようにできます。以下のように、問題のディレクティブIDに対してENABLEDをNOに設定します。このときAUTO_DROPも無効にしないと、SPDを削除後に再度作成されてしまうので注意してください。

SQL> EXEC dbms_spd.alter_sql_plan_directive(<ディレクティブID>,'ENABLED','NO');
SQL> EXEC dbms_spd.alter_sql_plan_directive(<ディレクティブID>,'AUTO_DROP','NO');

SPDが無効になっているかの確認は以下のように行います（LAST_USEDは、最後に使用した日時になります）。

ただし、ディレクティブIDを求める必要があります（以下のSQLは、問題になっているSQLで使用している表のディレクティブIDを求めています）。このとき問題になるのは、状態が’Permanent’のSPDになりますが、すべてのSPDが使用されるとは限らないので注意してください。

SQL> SELECT DISTINCT TO_CHAR(directive_id) dir_id,object_name obj_name,
  2         state||'('||EXTRACTVALUE(d.notes,'/spd_note/internal_state')||')' state,reason
  3    FROM dba_sql_plan_directives d INNER JOIN dba_sql_plan_dir_objects USING(directive_id)
  4   WHERE object_name IN ('TEST1') ;

DIR_ID               OBJ_NAME STATE                 REASON
-------------------- -------- --------------------- ------------------------------------
15793819631681001063 TEST1    USABLE(PERMANENT)     JOIN CARDINALITY MISESTIMATE
14539160301082858666 TEST1    USABLE(PERMANENT)     GROUP BY CARDINALITY MISESTIMATE

SQLで使用しているSPDの数は、実行計画のNote部で確認できます（以下は、1個のSPDを使用していることを示しています）。

以下のように、DBMS_XPLAN.DISPLAYファンクションを実行することで、SQLで使用しているSPD情報を出力できます。ただし、バインド変数を使用していると’Used directive ids:’は出力されません（これはマニュアルに載っている標準機能ではないので、それを認識して使用してください）。

問題になっているのは、OPTIMIZER_ADAPTIVE_FEATURES初期化パラメータのデフォルトがTRUEのため、この機能のことを知らずに使用している場合が多いようです。そのため、Oracle12cR2からは、適応統計のデフォルトがFALSEになるようです（Oracle12cR2の詳細は、リリースされてから説明しようと思います）。

3. おわりに

今回は適応問合せ最適化について説明しましたが、少しは参考になりましたでしょうか。今年も頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。

ページトップへ戻る▲ 

津島博士のパフォーマンス講座　Indexページ ▶▶