ITコーディネータのシュウです。

ゴールデンウイークも終わり、ときに夏を思わせるような暑い日もある5月も後半になりました。
上の写真は、ゴールデンウイークに家族で群馬県館林市にあるつつじが岡公園のつつじまつりに行ってきたときのものです。
樹齢800年を超えるヤマツツジの巨樹群が自然形のままで保存され、歴史的価値も高いとのことです。時期的には少し遅かったので、見頃の時期は過ぎてしまっていましたが、それでもまだ咲いているつつじは結構あって、たくさんのお客様で賑わっていました。海外の人も結構いましたね！
天気がよくて気持ちよかったです。ちょっとでも自然に触れるのはいいものですね。
なかなか家族サービスができない現状だけど、少しは喜んでくれたかな(^^!

＜本日の題材＞
バインド変数（Oracle）

SQLでパフォーマンスを改善させる方法、及びセキュリティの観点からSQLインジェクションを防ぐ方法ということで、バインド変数を使うということがよく話題に上がります。今回は、Oracle環境でのバインド変数について取り上げてみたいと思います。
バインド変数とは、SQL文に埋め込む変数のことで、実際の値を後から設定できるようにSQL文の一部を変数にしたもので、これを利用することで、SQL文の記述を統一する（データベースからみて同一のものとみなせる）ことができます。

OracleがSQLを実行するステップは、大きくは以下のようになっています。
・解析処理（PARSE）
・実行処理（EXECUTE）
・データの取り出し処理（FETCH：SELECT文のときのみ）

解析処理には、構文チェック、表・列の定義チェック、権限チェック、実行計画の生成などが含まれ、一度解析したSQLは実行計画とともにSGAの共有プールのライブラリキャッシュにキャッシュされます。
SQLが発行されると、Oracleはライブラリキャッシュに同一のSQLの解析結果がキャッシュされていないかを確認し、キャッシュされていればそれ以降の解析処理をスキップして、キャッシュ上の実行計画を使ってSQLを実行します。（このことをSOFT PARSEと呼びます）
同一の解析結果が存在しない場合、上記の解析処理を実行しますが、このHARD PARSEが比較的重たい処理のために、これを省くことができればスループットの向上に繋がります。

そして、この共有プールにキャッシュされた情報は、SQLが完全に一致するときのみ再利用されるため、条件値が異なったり、SQL文の大文字・小文字や改行位置、スペースの個数など一部でも異なると違うSQLとして新しく解析されてしまいます。こういう仕組みのため、バインド変数を使うことで同一のSQLとみなせれば、HARD PARSEを回避できるというメリットがあります。
（ちなみに、SQLのソースコードの中に、直接記述した数値や文字列のことをリテラルと呼びます）

具体的に試してみます。
以前、バルク処理というところで作成した顧客テーブル（20万件）を使って、SELECT文の処理速度をリテラルの場合とバインド変数を使った場合で比較してみます。

テーブル定義は以下：

CREATE TABLE customer(
 c_id number(8)
,c_name varchar2(20)
,Constraint PK_customere Primary key(c_id));

テストデータを作成します。

BEGIN
 FOR i IN 1..200000 LOOP
   INSERT INTO customer(c_id, c_name) VALUES(i, '顧客名_'||LPAD(TO_CHAR(i),8,'0'));
 END LOOP;
END;
/

このデータを、バインド変数を使わずに、主キーである c_id 列の値で SELECTしてみます。以前、実行の度にSQLが変わる場合について、動的SQLのEXECUTE IMMEDIATE文を使用する方法について取り上げたことがありましたが、今回もそれを使ってみます。

時間を計測したいので、時間の計測を可能にします。
SET TIMING ON

BEGIN
   FOR I IN 1..200000 LOOP
    EXECUTE IMMEDIATE 'SELECT * FROM customer WHERE c_id = ' || TO_CHAR(I);
   END LOOP;
END ;
/

3分2秒かかりました。

それでは、次は c_id = の条件のところを、バインド変数を使って行ってみます。
処理時間を厳密に確認するために、一旦、バッファキャッシュ、共有プールの情報をクリアします。

ALTER SYSTEM FLUSH  BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

先ほどのSELECTの処理を、バインド変数を使って実行します。
 
BEGIN
    FOR I IN 1..200000 LOOP
        EXECUTE IMMEDIATE 'SELECT * FROM customer WHERE c_id =:C1' USING I;
             -- :C1 のところがバインド変数 、USING I で変数 I を使用
    END LOOP;
END ;
/

5秒あまりで処理が終了しました。全然違いますね。

実際に、for loop 処理で生成したSELECT文について、共有SQL領域についての統計情報を示すV$SQLを抽出して確認してみます。 

select count(*) from V$SQL where SQL_TEXT like 'SELECT * FROM customer WHERE c_id%';

結果は1件と出ましたので、20万回のSELECT文がすべて同じ1回のSQL文を共有したということがわかります。実行計画を生成する HARD PARSEは1回のみであり、残りはすべて共有プール上のその実行計画を使って行ったということですね。

今回は、バインド変数を使うことで処理が非常に高速化しましたが、そうならないケースもあるようです。それについては、今回は特には記述はしませんが、また機会があれば確認してみたいと思います。

今日は以上まで

にほんブログ村