OPTIM PEEK USER BINDS

Syntax  :  _OPTIM_PEEK_USER_BINDS

Default :  TRUE

設定方法 : ・Parameter File
　　　　　 ・ALTER SYSTEM SET"_OPTIM_PEEK_USER_BINDS"=

サポート : 9i、10g

Bind Variable Peeking（以下BVP。バインド変数のピーキング）機能を有効にするかどうかを決定します。BVPとBind変数を含むSQL文の最適化を実行するとき、Bind変数が指す値を参照することを意味しす。

Oracleは、BVPを介してSQL文の実行時Bind値を利用するので、最適な実行計画を策定することができます。

BVPを使用する場合、Explain Planステートメントを介して確認した実行計画（Execution Plan）が、実際の運用環境では、適用されないことがあります。Explain Planステートメントでは、BVPが適用されないため、BVPが適用されたランタイムの実行計画とは異なる実行計画を報告する可能性があります。

もし、テスト環境では正常に実行されたSQL文が実行時に遅い場合、一次的にBVPによるサイドエフェクトがないか検証してなければなりません。ランタイムの実行計画は、V$ SQL_PLANビューで確認可能です。

BVPとHistogramの組み合わせにより、予期せぬパフォーマンスの問題が発生する場合がしばしばあります。次のような場合を例に挙げてみます。

・　Table AのColumum Cは「x」の値が1000個、「y」の値が1つです。このような類のColumnが傾いている
　　と表現します。
・　Column Cには、Indexが生成されており、Histogramが生成されています。
・　SELECT* FROM a WHERE c=：B1文章を実行します。

上記のような状況でBVPが適用される場合は、次のような問題が発生します。

・　BVPが適用される時点で：B1の値が「x」であったとするとFull Table Scanが実行されるでしょう。
　　以降の時点で：B1の値が「y」が指定されている場合には、Index Range Scanを使用する方がはるかに望ましい
　　にもかかわらず、すでに作成されたFull Table Scanを使用することになる問題が発生します。
・　BVPが適用される時点で：B1の値が「y」であったとするとIndex Range Scanが実行されるでしょう。
　　以降の時点で：B1の値が「x」が指定されている場合には、Full Table Scanがはるかに望ましいにもかかわらず、
　　既存のに生成されたIndex Range Scanを使用することになる問題が発生します。

つまり、Historgramが生成されたColunmが傾いており、このColumnのBVPが適用されると、Bind変数の値に応じてパフォーマンスが一定しない問題が発生することになります。この問題の解決方法は以下の通りです。

・　Histogramが生成されたColumnにはLiteral文を使用します。

　SELECT* FROM a WHERE c='x'

・　_OPTIM_PEEK_USER_BINDSオプションの値をFALSEに設定します。

統計情報を収集するためにDBMS_STATSパッケージを使用している場合はMETHOD_OPTパラメータのDefault値により10gの予期せぬ問題が発生することがあります。

Oracle9iまではMETHOD_OPTパラメータのデフォルト値がFOR ALL COLUMNS SIZE1です。つまり、Histogramを生成しないことが基本方式となります。しかし、Oracle10gからこのパラメータのデフォルト値がFOR ALL COLUMNS SIZE AUTOに変更されました。つまり、Histogramの作成するかどうかをOracleが判断することになります。
これらのバージョン間の変化は、次のような状況で問題を起こすことがあります。

・Oracle9iでOracle10gへのアップグレードを実行します。
・DBMS_STATSパッケージを利用して統計情報を収集します。
・METHOD_OPTパラメータの値を指定せずにDefault値を使用します。

このような状況では、以前はHistogramがなかったカラムにHistogramが生成されることができ、このカラムにBVPが適用される場合、実行計画が変更されたSQL文のパフォーマンスが大幅に低下する現象が発生することがあります。この問題の解決方法は以下の通りとなります。

・METHOD_OPTパラメータの値をDefault値を使用せずに明確に指定する。

例：
　　BEGIN
         DBMS_STATS.GATHER_table_STATS（OWNNAME=> 'U'、
                TABNBAM=> 'T'、
                METHOD_OPT=> 'FOR COLUMNS SIZE10 column_1'）;
    END;
　　/

上記のBVPとHistogram問題のためBVP機能をオフにしまう場合が多くあります。幸いなことにOracle11gでHistogramによるサイドエフェクトの問題が解決されました。

1.Oracle11gでは、Bind-aware cursor matchingと呼ばれる機能が追加されました。
  文字通りCursorを比較すると、bindを認識するという意味です。
2.Histogramが生成されたColumnのBVPが使用されている場合は、そのSQL CursorはBind-Sensitive Cursor
  に指定され、このCursorに対してBind値に基づいて実行計画の変動が大きいと判断されると、
  Bind-aware cursor matchingが有効になります。
3.Bind-aware cursor matchingが有効な場合、Bind Peekingが発生してもBind値に応じて異なる最適な
  実行計画を持つことになります。

1. V$ SQL_BIND_DATAビュー
2. V$ SQL_BIND_CAPTUREビュー

1. Oracle 11g Performance Tuning Guide