パフォーマンスを向上させるSQLの記述法：Oracle SQLチューニング講座（6）（3/3 ページ）
本連載では、Oracleデータベースのパフォーマンス・チューニングの中から、特にSQLのチューニングに注目して、実践レベルの手法を解説する。読者はOracleデータベースのアーキテクチャを理解し、運用管理の実務経験を積んでいることが望ましい。対象とするバージョンは現状で広く使われているOracle9iの機能を基本とするが、Oracle 10gで有効な情報も随時紹介していく。（編集局）
バインド変数化による解析情報の共有
前述の例1のようなSQLでは、異なる値を指定した処理が複数回行われた場合、WHERE句の条件列の値がリテラルで指定されているため（WHERE s_suppkey=1の部分）、改行の位置などを統一しただけでは同一のSQLとして解析されません。このような場合には、リテラル部分にバインド変数を使用することでSQLの記述を統一し、HARD PARSEを抑制することができます。
リテラルを使用したSQLとバインド変数を使用したSQLを1万回繰り返し行った場合にどのような違いが出るかを確認します。図2はリテラルで記述したSQLを実行、図3はバインド変数を使用したSQLを実行して取得したSQLトレース内のリカーシブコールに関する結果です。HARD PARSEの発生の有無によってリカーシブコールの発生回数に違いが現れています。
図2、図3の「Execute」はともに10001回ですが、「Parse」は図2が10001回で図3が2回となっています。また、両方の図にある「Parse」の「cpu（CPU時間）」「elapsed（経過時間）」を見るとバインド変数を使用した方（図3）は、ほとんどParseに時間を要していないことが分かります（なお、TKPORFの結果は、0.01秒未満は出力されません）。「total」で見ても、バインド変数を使用したSQLの方（図3）が全体の処理時間が圧倒的に短いことが分かります。
今回のSQLは単一表に対するSQLですが、複数の表にアクセスしたり、ほかのスキーマの表をアクセスしたりする場合には、さらに多くのリカーシブコールが実行されるので、できる限りバインド変数化するようにします、
図4のグラフは上記の結果をグラフにしたものです。
注意点
バインド化によるメリットを考慮する
実行回数が少ないSQLや、解析時間に比べて実行時間が長いSQLなどは、バインド化によるメリットは小さいものとなります。既存のSQLのバインド化を検討する場合、まずは、大量に実行されている類似SQLを対象としてください。
なお、データの偏りが大きく、ヒストグラム統計情報を取得している場合には、WHERE条件をリテラルで指定した方がよりよい実行計画が選択される可能性が高くなります。
Oracle9i以降の仕様変更
Oracle9iより前のバージョンでは、バインド変数を使用したSQLは変数にセットされた値を考慮せずに実行計画を決定していましたが、Oracle9iからハードパース時にバインド変数にセットされていた値を考慮して実行計画を決定するように変更されています（バインドピーク機能）。そのため、ハードパース発生を契機に、以前と異なる実行計画が採用されることがあります。
運用中に実行計画が変わらないようにするためには、「_optim_peek_user_binds」パラメータを「FALSE」に設定し、バインドピーク機能を無効にします。バインドピーク機能を無効化することで、以前のバージョンのように、バインド変数の値を考慮せずに実行計画が決定されます。
なお、このパラメータは隠しパラメータであり、またインスタンス全体に影響する設定であることから、すでに運用中のシステムの場合、十分な調査、テストを実施する必要があります。
カーソルの共有について
類似したSQL文が繰り返し実行されていることが分かっても、アプリケーションの変更や修正ができない場合もあり得ます。このような場合、Oracleの共有カーソル機能を使用することで、類似したSQLを内部的にバインド変数化し、解析結果を共有させることができます。
この機能により、バインド変数を使用していないSQLでも解析時間が削減されることによる応答時間の短縮、ラッチ競合の減少、システムリースの使用率減少が期待できます。
共有カーソル機能を使用するためには、初期化パラメータ「CURSOR_SHARING」を設定します。設定可能な値と機能は以下のとおりです。
- FORCE：リテラルがわずかに異なっても、同一のSQLとして共有します。リテラルがわずかに異なっても、同一のSQLとして共有します。
- SIMILAR：実行計画が同一であれば、同一のSQLとして共有します。実行計画が同一であれば、同一のSQLとして共有します。
- EXACT：完全に同一のテキストを含むSQLのみ共有します。（デフォルト値）。完全に同一のテキストを含むSQLのみ共有します。（デフォルト値）。
次回は、索引を使用する場合の注意点などを説明します。
Copyright © ITmedia, Inc. All Rights Reserved.