値（リテラル）のみが異なるようなSQLを繰り返し実行する場合、バインド変数を利用することによりSQLが共有され、パフォーマンスが向上するというのは、データベースのパフォーマンスチューニングでは常識とされています。
しかし、OracleにはSQLを発行する側で特に考慮しなくともOracleが勝手に判断してSQLに含まれるリテラルを自動的にバインド変数化し、SQLを共有してくれる機能があります。
この機能は初期化パラメータCURSOR_SHARINGで設定します。
CURSOR_SHARINGの設定値とその動作は以下の通りです。
| 設定値 | 動作 |
|---|---|
| EXACT | 完全に一致しなければSQLを共有しない（デフォルト） |
| FORCE | SQLの実行計画に変化があっても類似したSQLを共有する。 |
| SIMILAR | SQLの実行計画が変化していなければ類似したSQLを共有する。 |
動的SQLなどでリテラルを埋め込んだSQLを生成・実行することが多い場合、CURSOR_SHARINGの設定値を'FORCE'または’SIMILAR'と設定することにより、SQLのパフォーマンスが向上する可能性があります。
実際にどのような動作になるのか、そしてどの程度パフォーマンスが向上するのか実験してみました。
まず、適当なテーブル（TABLE1）に適当なデータを10,000レコード用意し、これをカーソルを使って１レコードずつ順番に読み込み、VALUES句の値を全てリテラルで埋め込んだINSERT文を動的に生成してTABLE1と同一レイアウトのテーブル（TABLE2）に登録するという処理を以下のように作成しました。
DECLARE
wk_sql VARCHAR2(1000);
CURSOR tab1_cur IS
SELECT * FROM TABLE1;
tab1_rec tab1_cur%ROWTYPE;
BEGIN
OPEN tab1_cur;
LOOP
FETCH tab1_cur INTO tab1_rec;
EXIT WHEN tab1_cur%NOTFOUND;
wk_sql := 'INSERT INTO TABLE2 VALUES ( '
|| TO_CHAR(tab1_rec.COL1)
|| ',''' || tab1_rec.COL2
|| ''',''' || tab1_rec.COL3
|| ''',' || TO_CHAR(tab1_rec.COL4)
|| ',TO_DATE(''' || TO_CHAR(tab1_rec.COL5,'YYYYMMDDHH24MISS')
|| ''',''YYYYMMDDHH24MISS'')'
|| ')';
EXECUTE IMMEDIATE wk_sql;
END LOOP;
CLOSE tab1_cur;
END;
/
そして、この処理をCURSOR_SHARINGの設定を変えて実行し、処理時間の差を比較しました。
処理前には公平を期すために、以下のコマンドを実行して共有プールをクリアし、
alter system flush shared_pool /
以下のSQLを実行して共有SQL領域内に実行しようとしているSQLが存在しないことを確認し、
select substr( sql_text, 1, 80 ) SQL_TEXT from v$sql where sql_text like 'INSERT INTO TABLE2%' /
以下のコマンドを実行してCURSOR_SHARINGの設定を変更し、
alter system set cursor_sharing = ～ /
上記の処理を実行しました。
その結果、各設定値での処理時間は以下のようになりました。
| 設定値 | 動作 |
|---|---|
| EXACT | 9.07秒 |
| FORCE | 4.05秒 |
| SIMILAR | 4.05秒 |
'FORCE'または'SIMILAR'と設定するとパフォーマンスが'EXACT'の２倍以上に向上しています。
また、処理実行後の共有SQL領域を確認してみると、'EXACT'の場合は以下のように値の異なるINSERT文が延々と出てきましたが、
SQL_TEXT
----------------------------------------------------------------------------------------------------
INSERT INTO TABLE2 VALUES ( 2849,'V','EWGBGFENMBOPCXJPCFJJDPCHKLKPBCOMZHDGR',6,T
INSERT INTO TABLE2 VALUES ( 2902,'J','SN',9918,TO_DATE('20051109033818','YYYYMMD
INSERT INTO TABLE2 VALUES ( 3711,'HV','BJVGHVLTWISGIDAUHDH',5129,TO_DATE('200506
INSERT INTO TABLE2 VALUES ( 3632,'AI','QRBKHMRZSVZEVUNLNUKICMPVDSCNIUVPWCDIPJJNF
INSERT INTO TABLE2 VALUES ( 3035,'NB','KZYR',63,TO_DATE('20051011221127','YYYYMM
INSERT INTO TABLE2 VALUES ( 3365,'RXZHLFW','BSKXGQWPFVN',123,TO_DATE('2005021717
INSERT INTO TABLE2 VALUES ( 3809,'CECBDCSIWM','XMFZEGTADTBPSUQKWTZNDHWTZR',1,TO_
INSERT INTO TABLE2 VALUES ( 1140,'YQ','NQAVHPXQHSAANHZJKCCHESCVDSYBRQGGFGVSKYXN'
INSERT INTO TABLE2 VALUES ( 8037,'QFBYSSN','QWFBJCNOMERSJMMWAGETTTSBNFXWWMZHUCIZ
.
.
.
'FORCE'、'SIMILAR'の場合は以下のようにリテラル部分が"SYS_B_n"というバインド変数に置き換えられたINSERT文が１レコードだけ出てきただけです。
SQL_TEXT ---------------------------------------------------------------------------------------------------- INSERT INTO TABLE2 VALUES ( :"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",TO_DATE(
このように何も考えずにプログラミングしていても、Oracleがよきに計らってくれるかなり優れものの機能なのですが、だからといって、「バインド変数など使わなくても．．．」と言ってしまうのはいかがなものかと思われます。
バインド変数を利用する目的はSQLの共有だけではなく、値の型変換をしなくても済むという利点もあるからです。
例えば、上記の処理でもSQLにリテラルを埋め込むために、データ型に応じて引用符でくくったり、変換関数をかましたりしていますが、バインド変数を使えばそのまま代入でき、型変換のオーバーヘッドがなくなる分パフォーマンスは向上します。
DECLARE
wk_sql VARCHAR2(1000);
CURSOR tab1_cur IS
SELECT * FROM TABLE1;
tab1_rec tab1_cur%ROWTYPE;
BEGIN
OPEN tab1_cur;
LOOP
FETCH tab1_cur INTO tab1_rec;
EXIT WHEN tab1_cur%NOTFOUND;
wk_sql := 'INSERT INTO TABLE2 VALUES ( :1, :2, :3, :4, :5 )';
EXECUTE IMMEDIATE wk_sql
USING tab1_rec.COL1, tab1_rec.COL2, tab1_rec.COL3, tab1_rec.COL4, tab1_rec.COL5;
END LOOP;
CLOSE tab1_cur;
END;
/
ちなみにこのように修正しての処理時間は３秒ほど。
確かに若干速くなっています。
ということで、やはりSQLを発行する側でバインド変数を利用するように心掛けることはプログラミングの基本として外せません。
CURSOR_SHARINGの機能は、他社が作成したソフトウェアを利用している場合や、上記のような動的SQLでバインド変数の数が可変である場合など、バインド変数化したくてもどうしようもない場合のための救済手段だと考えるべきでしょう。
【関連エントリ】
[Oracle] 類似したSQLを強制的に共有する（CURSOR_SHARING） 2005/05/26
[Oracle] CURSOR_SHARING=FORCEの環境でORA-01008エラーが発生 2005/06/06