0 次 INSERT SQL 第一次运行的耗时 Elapsed time was 322 miliseconds.
batch size= 1000 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 131 miliseconds.
BATCH update 第一次运行的耗时 Elapsed time was 80 miliseconds.
以下为DB SERVER端 10046 trace的结果：
begin
--我是一个拼接起来的SQL匿名块
insert into insertit values(:1 ,:2 ,:3 ,:4 );
insert into insertit values(:5 ,:6 ,:7 ,:8 );
insert into insertit values(:9 ,:10 ,:11 ,:12 );
insert into insertit values(:13 ,:14 ,:15 ,:16 );
insert into insertit values(:17 ,:18 ,:19 ,:20 );
insert into insertit values(:21 ,:22 ,:23 ,:24 );
insert into insertit values(:25 ,:26 ,:27 ,:28 );
insert into insertit values(:29 ,:30 ,:31 ,:32 );
insert into insertit values(:33 ,:34 ,:35 ,:36 );
insert into insertit values(:37 ,:38 ,:39 ,:40 );
insert into insertit values(:41 ,:42 ,:43 ,:44 );
insert into insertit values(:45 ,:46 ,:47 ,:48 );
insert into insertit values(:49 ,:50 ,:51 ,:52 );
insert into insertit values(:53 ,:54 ,:55 ,:56 );
insert into insertit values(:57 ,:58 ,:59 ,:60 );
insert into insertit values(:61 ,:62 ,:63 ,:64 );
insert into insertit values(:65 ,:66 ,:67 ,:68 );
insert into insertit values(:69 ,:70 ,:71 ,:72 );
...................................
insert into insertit values(:59989 ,:59990 ,:59991 ,:59992 );
insert into insertit values(:59993 ,:59994 ,:59995 ,:59996 );
insert into insertit values(:59997 ,:59998 ,:59999 ,:60000 );
insert into insertit values(:60001 ,:60002 ,:60003 ,:60004 );
commit ; end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 176.10 179.33 0 97 0 0
Execute 2 150.51 155.37 2 4 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 326.61 334.71 2 101 0 2
===>这是拼接SQL 在DB SERVER端总耗时334秒， CPU时间 326秒
insert into insertit
values
(:1 ,:2 ,:3 ,:4 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 32 0.09 0.11 4 823 1000 30002
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 34 0.09 0.11 4 823 1000 30002
==》这是 使用PreparedStatement.setExecuteBatch的结果， 耗时0.11秒，cpu时间 0.09秒,
因为batch size是1000，所以实际是每1000次INSERT执行一次，所以总的执行次数约为30次
insert into insertit
values
(:1 ,:2 ,:3 ,:4 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.03 0.04 1 93 475 15001
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.04 1 93 475 15001
==>这是使用addBatch()+executeBatch()， execute和parse均只为一次，即15000条数据仅解析一次 执行一次
类似于PL/SQL中bul