パフォーマンスを向上させるSQLの記述法：Oracle SQLチューニング講座（6）（3/3 ページ）

» 2004年11月20日 00時00分公開

[亀田明裕，株式会社アゲハ]

バインド変数化による解析情報の共有

　前述の例1のようなSQLでは、異なる値を指定した処理が複数回行われた場合、WHERE句の条件列の値がリテラルで指定されているため（WHERE s_suppkey=1の部分）、改行の位置などを統一しただけでは同一のSQLとして解析されません。このような場合には、リテラル部分にバインド変数を使用することでSQLの記述を統一し、HARD PARSEを抑制することができます。

　リテラルを使用したSQLとバインド変数を使用したSQLを1万回繰り返し行った場合にどのような違いが出るかを確認します。図2はリテラルで記述したSQLを実行、図3はバインド変数を使用したSQLを実行して取得したSQLトレース内のリカーシブコールに関する結果です。HARD PARSEの発生の有無によってリカーシブコールの発生回数に違いが現れています。

図2　リテラルで記述したSQL

図3　バインド変数を使用したSQL

　図2、図3の「Execute」はともに10001回ですが、「Parse」は図2が10001回で図3が2回となっています。また、両方の図にある「Parse」の「cpu（CPU時間）」「elapsed（経過時間）」を見るとバインド変数を使用した方（図3）は、ほとんどParseに時間を要していないことが分かります（なお、TKPORFの結果は、0.01秒未満は出力されません）。「total」で見ても、バインド変数を使用したSQLの方（図3）が全体の処理時間が圧倒的に短いことが分かります。

　今回のSQLは単一表に対するSQLですが、複数の表にアクセスしたり、ほかのスキーマの表をアクセスしたりする場合には、さらに多くのリカーシブコールが実行されるので、できる限りバインド変数化するようにします、

　図4のグラフは上記の結果をグラフにしたものです。

図4　バインド変数とリテラルによる処理速度の違い

注意点

バインド化によるメリットを考慮する

　実行回数が少ないSQLや、解析時間に比べて実行時間が長いSQLなどは、バインド化によるメリットは小さいものとなります。既存のSQLのバインド化を検討する場合、まずは、大量に実行されている類似SQLを対象としてください。

　なお、データの偏りが大きく、ヒストグラム統計情報を取得している場合には、WHERE条件をリテラルで指定した方がよりよい実行計画が選択される可能性が高くなります。

Oracle9i以降の仕様変更

　 Oracle9iより前のバージョンでは、バインド変数を使用したSQLは変数にセットされた値を考慮せずに実行計画を決定していましたが、Oracle9iからハードパース時にバインド変数にセットされていた値を考慮して実行計画を決定するように変更されています（バインドピーク機能）。そのため、ハードパース発生を契機に、以前と異なる実行計画が採用されることがあります。

　運用中に実行計画が変わらないようにするためには、「_optim_peek_user_binds」パラメータを「FALSE」に設定し、バインドピーク機能を無効にします。バインドピーク機能を無効化することで、以前のバージョンのように、バインド変数の値を考慮せずに実行計画が決定されます。

　なお、このパラメータは隠しパラメータであり、またインスタンス全体に影響する設定であることから、すでに運用中のシステムの場合、十分な調査、テストを実施する必要があります。