最適な実行計画が変わらないSQLの代表は、インデックスを使用した1件以下のデータの取得です。これは抽出条件の値が何であろうと、インデックススキャンで行を取得することになるでしょう。このようなSQLの場合には、バインド変数にしても、パフォーマンスが劣化することはありません。(※2)

しかし、範囲検索を含む検索の場合には、対象範囲によって最適な実行計画が異なります。例えば、取得結果がごく少数になる条件の場合には、インデックスを使用したほうが速く、ほぼすべてのレコードの取得になる場合には、テーブルフルスキャンを行なったほうが速くなります(※3)。それぞれに応じた実行計画が選択されます。

CBOは本来統計情報と検索条件の内容の両方の情報を使用し、適切なアクセスパスを選択しようとします。しかしこれはバインド変数を使用しないSQLの場合の話です。

バインド変数を使用するSQLの場合、どの程度の量・割合のレコードを取得するのかの算出ができなくなります(※4)。よって、「最適な実行計画」を選択することができなくなり間違った選択をしてしまうことになります。

例えば、期間範囲指定の絞り込み条件を含むSQLを考えます。

このとき、バインド変数を使用していないSQLでは「処理日時 < 2005年1月31日」はインデックススキャンになります。また「処理日時 < 2011年5月30日」はテーブルフルスキャンになります。

バインド変数を使用したときにはどうなるのでしょうか。その説明の前に、一旦インデックススキャンとテーブルフルスキャンの話をさせてください。

インデックスを使用する検索では、まずインデックス情報を読み込み、対象のデータの物理アドレスを取得し、そのアドレスに対し1件1件アクセスします。

データの数が少ない場合にはインデックスを使用したほうが速くなります。逆にテーブルのほとんどのデータを取得するような検索では、表の全データを一旦すべてまとめてメモリ上に持ってきてそれから処理をするほうが速くなり、むしろインデックスを使用して1件1件取得すると非常に遅くなります。

…話を戻して、バインド変数を使用したときにはどうなるのでしょうか。バインド変数を使用すると、検索条件の値に関わらずインデックススキャンになります。そのため「処理日時 < 2011年5月30日」の条件で検索する場合には非常に遅くなってしまいます。

このようにバインド変数を使用しているせいで、CBOが適切な実行計画を選択することができずに、非常に遅くなるということがしばしばおこります。これをCBOのせいにする人もいるかもしれませんが、それは大きな間違いです。

適切な判断に必要な情報を与えていないのですから、適切な判断ができないのは当たり前です。適切な判断を与えないような使い方をしている使用者側(PGだかSEだか)に問題があるのです。

私の経験では、システム開発の現場ではどんな場合でも何も考えずにバインド変数を使用する、というルールで開発しているプロジェクトが少なからず存在します。

まとめ

Oracleのパフォーマンスを最適に保つ為に、バインド変数を使用すべきかどうかを考えた上で適切な判断しましょう。

(※1)ハードパースの回数が減る事の他に、以下の効果も考えられます。

(※2)

インデックススキャン：「INDEX RANGE SCAN」＋「TABLE ACCESS BY INDEX ROWID」

テーブルフルスキャン：「TABLE ACCESS FULL」

(※3)DBの経験が浅い人ですと、どんなときでもインデックスを使用すれば速いと思いこんでしまう人もいるようですが、それは大きな間違いです。理解せずにむやみにヒント句を付与して逆に遅くなることもありうるので注意しましょう。