【Oracle】バルクインサートでどれほど性能向上するか簡単な実験をしてみる
バルクの練習したついでに、ふと思い立って実験してみたくなったのでその実験結果を載せる。
内容は「バルクインサートを使うとどれくらい早くなる？」の簡単な実証実験である。
(1)以下のテーブル「TEST_TAB」を作成する。
create table TEST_TAB (
test_no number(10)
,test_name varchar2(300)
,test_memo varchar2(100)
,test_flg number(1)
)
/
(2)そんでこのテーブルにとりあえず100万件程適当にデータをブチ込む。
(3)同じレイアウトのテーブル「TEST_TAB_BULK」を作成する。
create table TEST_TAB_BULK as
select * from TEST_TAB where 0=1;
(4)以下のPL/SQLでバルク処理を実行する。
バルク化の件数はバインド変数とし、外部から引き渡す。（これはいろんなバルク化件数で実験したいため）
なお、面倒だから例外はシカトするｗ
declare
cursor cur_main is
select TEST_NO
,TEST_NAME
,TEST_MEMO
,TEST_FLG
from TEST_TAB
;
type typ_main is table of cur_main%rowtype index by binary_integer;
arr_main typ_main;
nmb_bulk_collect_count number(10) := :nmb_bulk_collect_count_bind;
nmb_ins_count number(10) := 0;
begin
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'yyyy/MM/dd HH24:mi:ss') || ' START (BULK COUNT:' || trim(to_char(nmb_bulk_collect_count)) || ')');
execute immediate 'truncate table TEST_TAB_BULK';
open cur_main;
loop
-- bulk fetch
fetch cur_main bulk collect into arr_main limit nmb_bulk_collect_count;
exit when arr_main.COUNT = 0;
begin
-- bulk insert
forall i in 1..arr_main.COUNT SAVE EXCEPTIONS
insert into TEST_TAB_BULK values arr_main(i);
commit;
exception
when others then
null;
end;
end loop;
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'yyyy/MM/dd HH24:mi:ss') || ' END');
end;
/
(5)「nmb_bulk_collect_count」をいろいろ変えてみて実験する。
とりあえず「1」「10」「100」「1000」「10000」を与えてみた結果はこちら。↓
バルク化件数処理時間
| 1 | 約103秒 |
| 10 | 約16秒 |
| 100 | 約6秒 |
| 1000 | 約5秒 |
| 10000 | 約5秒 |
これを見るとパフォーマンス的にはバルク化件数は1000くらい＝赤太字の箇所で性能向上としては打ち止めのようだ（バルク化件数10000と大差がない）。
ある程度まで来ると性能向上効果が薄れるとは知っていたが、思ったより小さい数値だなというのが感想である。
まあこの実験ではINとOUTが同じレイアウトだし、項目数も少ないし、こんなもんかもしれない。
とはいえ、実際のケースでもこういった観点でのバルク化件数の追及は必要であろう。
なお、処理時間に「約」とついているのは、PL/SQLで処理時間を測る目的で開始と終了に出力しているのが「秒」までだからである。
systimestamp使ってミリ秒まで出せばもっと正確に測れるのだが面倒くさいのでやっていない。ｗ
体感的にはバルク化件数1000の「約5秒」は5.2～5.3秒くらい、バルク化件数10000のほうは5.7～5.8秒くらいの感覚だった。
ただ、何度か試した感じでは両方とも5.5秒付近をうろついていたので、この辺がこの処理の性能限界というところなのだろう。
では、愚直に１件ずつループするとどうなるのか？
言い換えれば、バルク化の恩恵がどの程度得られているのか？
それを確かめてみよう。
begin
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'yyyy/MM/dd HH24:mi:ss') || ' START [NORMAL LOOP]');
execute immediate 'truncate table TEST_TAB_BULK';
for row_main in (select * from TEST_TAB) loop
insert into TEST_TAB_BULK values row_main;
end loop;
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'yyyy/MM/dd HH24:mi:ss') || ' END [NORMAL LOOP]');
end;
/
結果は「約31秒」となった。
うーんやはり大分遅いな、という印象である。
しかもこのケースでいうと、バルク化により「約5秒」まで処理時間が縮められる＝6倍くらい速く出来る、というのは驚異的な性能改善ポイントである。
大した件数でなければこの程度のPL/SQLでも十分だが、やはり大量件数を相手するにあたってはバルク化の考慮は絶対するべきである、というのがここからも明らかである。
なお、バルク化件数＝1の時結果は「約103秒」であり、この「愚直ループ」ケースよりむしろ3倍以上も遅い結果となっている。
単一レコードに対する処理内容がバルク化の方が多い分そりゃ遅くなるかもね、というのは直感的にはわかるが、ここまで違うとは、というのはちょっと驚いた。
まあバルク化するにあたって「1」なんて件数を指定する人はいないだろうが、面白い実験結果を見た気がする。
そういえばそもそも普通にSELECT+NSERTしたらどうなんだろ？って思って試してみた。
ダイレクトパスインサートである。
begin
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'yyyy/MM/dd HH24:mi:ss') || ' START [DIRECT PATH INSERT]');
execute immediate 'truncate table TEST_TAB_BULK';
-- direct path insert by adding "APPEND" hint clause
insert into TEST_TAB_BULK select /*+ APPEND */ * from TEST_TAB;
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'yyyy/MM/dd HH24:mi:ss') || ' END [DIRECT PATH INSERT]');
end;
/
結果は「約5秒」でバルク化時の性能限界（と思われる値）とほぼほぼ一致した。
このケースのようにINとOUTのテーブルのレイアウトが完全一致で、丸ごと中身入れ替えて良いようなケースなら、当然ながらこれが一番手っ取り早くて分かりやすくて速いんだろうね。
逆に言うとバルク化の限界でこれに近い速度が出せているのはチューニングの成果ともいえるかもしれない。
