e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第95回 「バインド変数、CURSOR_SHARINGパラメータの効果を測定してみる」

2014.05.01

こんにちは。インストラクターの蓑島です。
5月になりましたね。時の経過は年々速いものに感じます。

前回、文字列連結でSQL文を組み立てるとき(動的SQL)、表名や列名ではなく、「値」の部分については「バインド変数」が可能であり、それ用いることで様々なメリットがあることを解説しました。
例えば、多くのユーザで何度も実行する文であれば、解析処理の結果を再利用し、パフォーマンスが向上します。また、「値」しかセットできないので、SQL文の意味を変える悪意ある操作を防止できるのでセキュリティ対策にもなります。

今回はパフォーマンスの面に注目し、バインド変数を使うことで実際にパフォーマンスがよいことを測定します。また、バインド変数を使っていなくても、初期化パラメータをCURSOR_SHARING=FORCEと設定することで、かなりの程度、パフォーマンスが向上することも測定します。

ストーリーはこうです。

まず、大量の行数の表を用意します。
その表には、主キー列あり、その値は1からの連続番号だとします。
その表に対して主キーの値を条件に、1から順番にループ処理ですべての行をSELECTします。主キーが条件なので、実行計画は明らかで、必ず主キー索引を使った実行計画となります。

そのような処理を以下の3つのケースで行い、処理時間を測定します。

(1)バインド変数を使わない動的SQLの場合
(2)バインド変数を使った動的SQLの場合
(3)バインド変数を使っていないが、CURSOR_SHARING=FORCEと設定してある場合

もっともパフォーマンスがよいのが、バインド変数を使っている場合、つまり(2)となります。
次にパフォーマンスが良いのが、(3)です。
CURSOR_SHARING=FORCEと設定されているとSQL文中の値をバインド変数に置き換えて解析するので、値は違っても文としては同一であり、解析結果が共有されるからです。
最後に、一番パフォーマンスが悪いのが、(1)です。
この場合は、すべての行のSELECT文がそれぞれ違う文となるので、解析結果が共有されず、毎回、ハード解析となります。

では早速、実際に操作してみましょう。

まず、表を作成します。

1
2
3
4
5
CREATE TABLE TEST01
( ID  NUMBER,
   NAME VARCHAR2(10));
 
表が作成されました。

そして、そこに大量データをロードします。

1
2
3
4
5
6
7
8
BEGIN
   FOR I IN 1..100000 LOOP
     INSERT INTO TEST01 VALUES (I, 'ABC' );
   END LOOP;
END ;
/
 
PL/SQLプロシージャが正常に完了しました。

これでTEST01表に 100000行が格納されました。

ID列を主キーに設定します。これによりID列に主キー索引が自動的に作成されます。

1
2
3
ALTER TABLE TEST01 ADD PRIMARY KEY (ID);
 
表が変更されました。

最後に、オプティマイザ統計を収集し、オプティマイザが実行計画を最適化できるようにします。
(※DBMS_STATSに対するEXECUTE権限が必要です)

1
2
3
EXEC DBMS_STATS.GATHER_TABLE_STATS( 'SCOTT' , 'TEST01' )   -- SCOTTはスキーマ名(ユーザ名)です。
 
PL/SQLプロシージャが正常に完了しました。

これで準備ができました。
それではすべての行を主キー(ID列)列でSELECTして、効果を測定します。

まず、条件を厳密にするためメモリの情報をクリアします。(※ALTER SYSTEM権限が必要です)

――バッファキャッシュ(データのキャッシュ)をクリア

1
2
3
ALTER SYSTEM FLUSH  BUFFER_CACHE;
 
システムが変更されました。

――共有プール(実行計画のキャッシュ)をクリア

1
2
3
ALTER SYSTEM FLUSH SHARED_POOL;
 
システムが変更されました。

時間の測定を可能にします。
SQL> SET TIMING ON

では準備ができましたので、早速、パターン(1)すなわち、すべての行をバインド変数を使わない動的SQLでSELECTします。

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
   DUMMY  VARCHAR2(10);
BEGIN
   FOR I IN 1..100000 LOOP
    EXECUTE IMMEDIATE 'SELECT NAME FROM TEST01 WHERE ID = ' || TO_CHAR(I);
   END LOOP;
END ;
/
 
PL/SQLプロシージャが正常に完了しました。
 
経過: 00:00:39.19

処理に用した時間は、今回の私の環境では約39秒でした。
ここで、5行目の文字列 「'SELECT NAME FROM TEST01 WHERE ID = ' || TO_CHAR(I)」に着目します。
変数Iの値が 1,2,3・・とループしますので、この文字列は次のようなSQL文の文字列に組みあがります。

'SELECT NAME FROM TEST01 WHERE ID = 1'
'SELECT NAME FROM TEST01 WHERE ID = 2'
'SELECT NAME FROM TEST01 WHERE ID = 3'
・・・(略)・・・
値の部分が変数ではない(リテラル文字)ので、それぞれが違う文です。
したがって100000個の異なるSQL文となり、ハード解析の回数が100000回ということになります。

(注意)
このEXECUTE IMMEDIATE文にはINTO句がないので、SELECTした結果は変数に格納していません。
本来、変数に格納しないSELECT文を実行してもプログラミングとしては意味がありませんが、ここでは処理時間の測定のみを意図したものです。

では、もう一度、メモリの情報をクリアして、今度はパターン(2)、すなわちバインド変数を使った処理で同じことを行います。
――バッファキャッシュ(データのキャッシュ)をクリア

1
2
3
ALTER SYSTEM FLUSH  BUFFER_CACHE;
 
システムが変更されました。

――共有プール(実行計画のキャッシュ)をクリア

1
2
3
ALTER SYSTEM FLUSH SHARED_POOL;
 
システムが変更されました。

では、早速、バインド変数を使ったパターンで同じことを行います。

1
2
3
4
5
6
7
8
9
10
11
DECLARE
   DUMMY  VARCHAR2(10);
BEGIN
   FOR I IN 1..100000 LOOP
    EXECUTE IMMEDIATE 'SELECT NAME FROM TEST01 WHERE ID = :B1' USING I;
   END LOOP;
END ;
/
 
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:02.24

そうすると今度は約2秒ですね。
先ほど39秒かかっていた処理がわずかに2秒です。圧倒的な違いです。
ここで注目すべきは5行目の文字列'SELECT NAME FROM TEST01 WHERE ID = :B1'の中の「:B1」という記述です。これがバインド変数であり、その値が同じく5行目の USING I で指定された変数 I です。この場合、変数Iの値がなんであっても、SQL文としては同一の文となります。
したがって、ここではハード解析(実行計画を作る)は最初の1回しか行っていません。
先ほどのパターンではハード解析を100000回行ってましたが、今回はたったの1回です。このハード解析の回数の差が大きな処理時間の差となったわけです。

では最後に、パターン(3)すなわち、バインド変数を使っていないが、CURSOR_SHARING=FORCEと設定されているケースです。
――バッファキャッシュ(データのキャッシュ)をクリア

1
2
3
ALTER SYSTEM FLUSH  BUFFER_CACHE;
 
システムが変更されました。

――共有プール(実行計画のキャッシュ)をクリア

1
2
3
ALTER SYSTEM FLUSH SHARED_POOL;
 
システムが変更されました。

CURSOR_SHARING=FORCEと設定します。

1
2
3
ALTER SYSTEM SET CURSOR_SHARING = FORCE ;
 
システムが変更されました。

では、実行します。

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
   DUMMY  VARCHAR2(10);
BEGIN
   FOR I IN 1..100000 LOOP
    EXECUTE IMMEDIATE 'SELECT NAME FROM TEST01 WHERE ID = ' || TO_CHAR(I);
   END LOOP;
END ;
/
 
PL/SQLプロシージャが正常に完了しました。
 
経過: 00:00:06.49

今度は処理に要した時間は約6秒ですね。
当初の39秒からみるとやはり圧倒的に速いですが、しかしバインド変数を使っている場合(約2秒)には及びませんね。

この場合は、以下のように毎回違うSQL文が実行されています。
しかし解析時に値の部分をバインド変数に置き換えますので結果として文が共有され、ハード解析の回数は1回のみとなりパフォーマンスがよくなったのです。
'SELECT NAME FROM TEST01 WHERE ID = 1'   -- 値1がバインド変数に置換される
'SELECT NAME FROM TEST01 WHERE ID = 2'   -- 値2がバインド変数に置換される
'SELECT NAME FROM TEST01 WHERE ID = 3'   -- 値3がバインド変数に置換される

このように、バインド変数を使っていない動的SQLを何度も実行するシステムで、ハード解析がボトルネックとなっている状況では、CURSOR_SHARING=FORCEの設定をすることでパフォーマンスを改善できる可能性があります。ただ、最初に検討されるべきは、アプリケーションを構築段階から、何度も実行される動的SQL文に対してはバインド変数を使うことだと言えるでしょう。

それでは今回はここまでとします。
また次回、ご期待ください。

先頭へ戻る