Bulk Insert有無による性能差を、PL/SQL の FORALL文〜で計測してみる。
表題の通り、Bulk Insert有り／無しの性能差を
PL/SQL の FORALL文 で計測してみるやで 彡(ﾟ)(ﾟ)
まずは 索引付き で TABLE を作成します。
CREATE TABLE TBL_A ( C1 NUMBER , C2 DATE , C3 VARCHAR2(10) ); ALTER TABLE TBL_A ADD CONSTRAINT TBL_A_PK PRIMARY KEY(C1) USING INDEX; CREATE INDEX TBL_A_I1 ON TBL_A(C2);
初めは Bulk Insert を使用せず、普通の INSERT文 を
トレース取りながら実行(1,000,000行 INSERT)します。
ALTER SESSION SET TRACEFILE_IDENTIFIER = AYSHIBAT; EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE , binds => FALSE); BEGIN FOR i IN 1..1000000 LOOP INSERT INTO TBL_A VALUES ( i , TO_DATE('20150723', 'YYYYMMDD') + (i/24/60/60) , TO_CHAR(MOD(i, 1000)) ); END LOOP; COMMIT; END; / PL/SQL procedure successfully completed. Elapsed: 00:01:46.05 EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE;
SQLトレース(※tkporfによる整形後)の抜粋は以下の通り
SQL ID: awm154czu9vrz Plan Hash: 0 INSERT INTO TBL_A VALUES ( :B1 , TO_DATE('20150723', 'YYYYMMDD') + (:B1 /24/60/60) , TO_CHAR(MOD(:B1 , 1000)) ) ： call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1000000 46.48 59.71 0 16896 6539031 1000000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1000001 46.48 59.71 0 16896 6539031 1000000 ： Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 0 0 0 LOAD TABLE CONVENTIONAL (cr=2 pr=0 pw=0 time=384 us) ： Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ log file switch completion 35 0.35 3.83 log buffer space 41 0.64 9.63 latch: shared pool 3 0.00 0.00 ：
同じ事を、Bulk Insert(※PL/SQL の場合は FORALL文を使用)でやると…
SET TIME ON; SET TIMING ON; ALTER SESSION SET TRACEFILE_IDENTIFIER = AYSHIBAT; EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE , binds => FALSE); DECLARE TYPE t_array_A IS TABLE OF TBL_A%ROWTYPE INDEX BY BINARY_INTEGER; array_A t_array_A; BEGIN array_A.DELETE; FOR i IN 1..1000000 LOOP array_A(i).C1 := i; array_A(i).C2 := TO_DATE('20150723', 'YYYYMMDD') + (i/24/60/60); array_A(i).C3 := TO_CHAR(MOD(i, 1000)); END LOOP; FORALL i in array_A.FIRST..array_A.LAST INSERT INTO TBL_A VALUES array_A(i); COMMIT; END; / PL/SQL procedure successfully completed. Elapsed: 00:00:18.00 EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE;
すでに Elapsed の時点で 1:46⇒0:18 で速いんですが、
こいつのSQLトレース(※tkprofによる整形後)を見てみると……
SQL ID: 8k4spyp5j3c59 Plan Hash: 0 INSERT INTO TBL_A VALUES (:B1 ,:B2 ,:B3 ) ： call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 3.49 15.20 0 16957 154179 1000000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 3.49 15.20 0 16957 154179 1000000 ： Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 0 0 0 LOAD TABLE CONVENTIONAL (cr=16966 pr=0 pw=0 time=15178058 us) ： Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ log buffer space 39 1.76 10.09 log file switch completion 8 1.18 1.59 ：
Bulk Insert(FORALL〜)のが圧倒的に速いのですが、両者の最大の違いは
query+current値の多さ／少なさと、それに伴う CPU時間 の違いとなります。
当然ながら、処理量(読込量)が少ない方が、速いんやで 彡(^)(^)
★Bulk Insert不使用の場合 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1000000 46.48 59.71 0 16896 6539031 1000000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1000001 46.48 59.71 0 16896 6539031 1000000 ★Bulk Insertを使用した場合 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 3.49 15.20 0 16957 154179 1000000 ★読込量が少ない Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 3.49 15.20 0 16957 154179 1000000
索引付きの表にレコードをINSERTする場合、INSERTするレコードの
rowidを B-Tree索引 の どこのリーフに入れるか？ を
特定するための索引読込が発生します。
この索引読込が INSERT 1回 に付き 最低1ブロック 発生するので、
INSERT回数が少ない Bulk Insert の方が読込量が少なくなって、
性能が向上するんですね。
同じことを索引無しの状態で実行すると
★Bulk Insert不使用＆索引無しの場合 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1000000 30.26 30.73 0 6989 1040678 1000000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1000001 30.26 30.73 0 6989 1040678 1000000 ★Bulk Insertを使用＆索引無しの場合 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.66 0.69 1 4348 30249 1000000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.66 0.69 1 4348 30249 1000000
Bulkのが相変わらず速いけど、索引有りの時より差は縮まるんやで 彡(ﾟ)(ﾟ)
