バインド・ピーク機能
値（リテラル）のみが異なるような SQL を繰り返し実行する場合、バインド変数を利用することにより SQL が共有され、パフォーマンスが向上するというのは、データベースのパフォーマンスチューニングでは常識とされています。（Oracle9i からは CURSOR_SHARING という初期化パラメータを設定することによって、SQL に含まれるリテラルを自動的にバインド変数化し、SQL を共有させることもできますが、原則的には SQL を発行する側で考慮すべきでしょう。）
ただ、バインド変数化した場合、オプティマイザの挙動が気になります。
従来の Oracle では、リテラルで記述した SQL の場合はリテラル値を参考にして最適な実行計画を決定しますが、バインド変数化した SQL の場合はバインド変数にセットされた値を考慮せずに SQL の実行計画を決定していました。
しかし、Oracle 9i からは新たに追加されたバインド・ピークという機能によって、バインド変数化した SQL についてもバインド変数にセットされた値を参考にして最適な実行計画を決定するように変更されました。
これにより SQLをバインド変数化した場合でも、値を考慮したより現実的なアクセスパスが選択されるようになったわけです。
バインド・ピーク機能の落とし穴
しかし、このバインド・ピーク機能には落とし穴があります。バインド・ピークの機能が有効なのは、ハードパース時のみです。
バインド変数化によって共有された SQL は、初回実行時にハードパースされ、以降はその結果を再利用します。
つまり、初回実行時にバインド変数にセットした値に対して最適な実行計画を、2 回目以降の実行時にも適用し続けるということになるわけです。
この仕様は、バインド変数にどのような値をセットしてもヒット件数がそう変わらないケースについては全く問題ありませんが、バインド変数にセットする値によってヒット件数が著しく異なるようなケースでは、パフォーマンスに深刻な悪影響を与えることがあるため注意が必要です。
バインド・ピーク機能がパフォーマンスに悪影響を与える例
例えば、何でもいいんですが、以下のような商品テーブル（ITEM）があるとします。CREATE TABLE ITEM ( ITEMID NUMBER(7,0) NOT NULL, ITEMNAME VARCHAR2(40), PRICE NUMBER(6,0), COMMENTS VARCHAR2(400), CONSTRAINT ITEM_PK PRIMARY KEY (ITEMID) USING INDEX ) /このテーブルには 100万点の商品データが登録されています。
また、このテーブルには価格での検索を高速化するため、以下のインデックスを定義してます。
CREATE INDEX ITEM_IX_PRICE ON ITEM(PRICE) /このテーブルに対し、商品名に特定のキーワードを含む特定の価格範囲の商品数を算出するファンクションを作成し、
CREATE OR REPLACE FUNCTION GET_ITEM_COUNT
(
keyword VARCHAR2,
price_from NUMBER,
price_to NUMBER
)
RETURN NUMBER
IS
item_count NUMBER;
BEGIN
SELECT COUNT(*) INTO item_count FROM ITEM
WHERE PRICE BETWEEN price_from AND price_to
AND ITEMNAME LIKE '%' || keyword || '%';
RETURN item_count;
END;
/
以下のように'ABC'というキーワードを含む 1001円～100万円の商品数を算出し、続いて'ABC'というキーワードを含む 1000円以下の商品数を算出したところ、実行時間は以下のようになりました。SQL> SELECT GET_ITEM_COUNT('ABC',1001,1000000) FROM DUAL;
GET_ITEM_COUNT('ABC',1001,1000000)
----------------------------------
1042
経過: 00:00:01.56
SQL> SELECT GET_ITEM_COUNT('ABC',1,1000) FROM DUAL;
GET_ITEM_COUNT('ABC',1,1000)
----------------------------
1
経過: 00:00:01.42
両者ともほぼ 1.4～6秒くらいで大差ありません。この時の SQL の実行計画を見てみると、以下のように ITEM テーブルに対して全表走査が実行されています。
OPERATION OBJECT COST BYTES
---------------------------------------- --------------- ------ ----------
SELECT STATEMENT Optimizer=ALL_ROWS 7410
SORT AGGREGATE 26
FILTER
TABLE ACCESS (FULL) ITEM 7409 1298960
最初に実行した SQL の価格の範囲指定がテーブルの大半のレコードにヒットするものだったため、全表走査が効果的と判断されたようです。
そして、それ以降に実行する SQL は、どのように条件を変えても同じ実行計画で実行されることになります。
ただ、上記の例の場合は、全表走査が選択されていますから、ヒット件数の多少にはさほど影響されず、同じような実行時間となっています。
次に、上記の SQL の実行計画を破棄するために、一旦セッションを切断し、再接続した後、以下のコマンドを実行して共有プールをクリアします。
ALTER SYSTEM FLUSH SHARED_POOL;そして、今度は先ほどの SQL の順序を入れ替えて実行してみると、実行時間は以下のようになりました。
SQL> SELECT GET_ITEM_COUNT('ABC',1,1000) FROM DUAL;
GET_ITEM_COUNT('ABC',1,1000)
----------------------------
1
経過: 00:00:00.57
SQL> SELECT GET_ITEM_COUNT('ABC',1001,1000000) FROM DUAL;
GET_ITEM_COUNT('ABC',1001,1000000)
----------------------------------
1042
経過: 00:00:04.35
前者は 0.57秒と速くなりましたが、逆に後者は 4.35秒と激烈に遅くなっています。この時の SQL の実行計画を見てみると、ITEM テーブルに対してインデックス走査が使われています。
OPERATION OBJECT COST BYTES
---------------------------------------- --------------- ------ ----------
SELECT STATEMENT Optimizer=ALL_ROWS 1005
SORT AGGREGATE 26
FILTER
TABLE ACCESS (BY INDEX ROWID) ITEM 1004 1300
INDEX (RANGE SCAN) ITEM_IX_PRICE 5
最初に実行した SQL の価格の範囲指定がテーブルのごく限られた範囲のレコードにしかヒットしないものだったため、インデックス走査が効果的と判断されたようです。ただ、最初に実行した SQL は高速ですが、次に実行した SQL は価格の範囲指定がテーブルの大半のレコードにヒットするものであるにもかかわらずインデックスを使用するため、パフォーマンスが著しく悪くなっています。
このようにバインド変数にセットする値によってヒット件数が著しく異なるようなケースでは、バインド・ピーク機能がかえって仇となる可能性があります。
上記の例のような単一テーブルに対する単純な検索の場合はまだいいのですが、複数のテーブルを結合するような場合は、致命的なパフォーマンスの悪化を引き起こす可能性もありますので特に注意が必要です。