ループでinsertよりinsert selectが早い理由をstatspackで見てみる

ループでinsertを回数分だけ発行するより、insert into ... select ... の方が早い。後者はselect出来るものしか使えないので、比較対象としてはちょっと違うかもしれないけれども。逆に言えばselect出来るようにしてしまえばいいので、例えばファイル取り込みしたいときそのファイルを外部表にしてそこからselect insertする手もある。なので、比較することには一応の意味はあると考える。いやまぁ、もちろん根っこの動機は単なる好奇心なのだけど。

というわけで。比較は100万行作るやり方を2種類用意し、それぞれのstatspackレポートを採取する。

環境

DB
- CentOS-6.4-x86_64
- Oracle Database Express Edition 11g Release 2
ツール
- Oracle SQL Developer 4.0
- RLogin Version 2.14.7

準備

pl/sqlでループでinsertを100万回実行する方法のコード

テーブルを作る。

DROP TABLE hoge_table PURGE;
CREATE TABLE hoge_table 
(
  ID INTEGER NOT NULL 
, VALUE VARCHAR2(10) NOT NULL 
, CONSTRAINT hoge_table_pk PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

100万回insertするだけのpl/sqlを作る。

create or replace PROCEDURE INSERT_TO_HOGE_TABLE AS 
BEGIN
  FOR I IN 1..1000000 LOOP
    INSERT INTO HOGE_TABLE(ID, VALUE) VALUES(I, DBMS_RANDOM.STRING('X', 10));
  END LOOP;
  COMMIT;
END INSERT_TO_HOGE_TABLE;

実行するときのpl/sqlブロック

BEGIN
  INSERT_TO_HOGE_TABLE();
END;

insert selectで100万行追加する方法のコード

追加先のテーブルを作る。

DROP TABLE HOGE_TABLE2 PURGE;
CREATE TABLE hoge_table2 
(
  ID INTEGER NOT NULL 
, VALUE VARCHAR2(10) NOT NULL 
, CONSTRAINT hoge_table2_pk PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

pl/sqlで作った100万行のテーブルからinsert selectする。

INSERT INTO hoge_table2 SELECT ID, VALUE FROM hoge_table;
COMMIT;

実行前の準備

バッファキャッシュのクリア、ログファイルのスイッチをして、Oracleを再起動してからそれぞれの100万行追加のコードを実行する。

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM SWITCH LOGFILE;

実行直前と直後にstatspackのスナップショットを採取し、その間のレポートを作成する。

まず実行時間の比較。

種類	時間（秒）
pl/sql	156.235
insert select	28.312

statspackレポートの比較

両者のレポートの値を比較していく。上がループ、下がinsert selectとする。

Statistic - redo

Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
redo size                                520,566,416    2,344,893.8 ############
redo entries                               2,031,240        9,149.7    507,810.0
redo blocks written                        1,070,041        4,820.0    267,510.3
redo synch time (usec)                       179,133          806.9     44,783.3

Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
redo size                                116,963,632    1,624,494.9 ############
redo entries                                  74,949        1,041.0     18,737.3
redo blocks written                          235,382        3,269.2     58,845.5
redo synch time (usec)                       282,029        3,917.1     70,507.3

redo sizeが約5倍の差がついている。実行時間の差もそのくらいなので、おおよそredo sizeで説明がつくと考えられる。

同じ結果を生むにも関わらず、redo sizeに差がつくのは何故か。まず、SQL（insert）の実行回数がかたや100万回、かたや1回である。Oracleは基本的にはSQL単位で物事が進むので、回数が少ない方がより有利なのは想像がつく。insert + データ, insert + データ, insert + データ, ... × 100万回と、insert select 100万行 × 1回とでは、insert回数のオーバーヘッド分安く済む、ということだろう。

また、当然ながらログスイッチの回数も少なくなる。

Statistic                                      Total  per Hour
--------------------------------- ------------------ ---------
log switches (derived)                            11    178.38

Statistic                                      Total  per Hour
--------------------------------- ------------------ ---------
log switches (derived)                             2    100.00

Statistic - undo

undo change vector size                  164,161,440      739,466.0 ############

undo change vector size                   40,939,144      568,599.2 ############

下記参照の通り、insertは大してUNDOを消費しないのだが、insert into .. selectはもっと少なくて済んでいることが分かる。

・INSERT文
INSERTされるROWID情報（INSERT INTO ... SELECT文の場合は、SELECT結果行数だけが該当する）。
UNDO管理でOracleのデータを守る − ＠IT自分戦略研究所より抜粋

感想とか

とまぁそんなわけで、トランザクションの性能はredoの量が一つの鍵なんだろうな、といったところが分かります。

kagamihogeの日記

kagamihogeの日記です。