PLSQL批量Forall操作性能提升详解

--事实证明，使用bulk操作对比普通单条执行来说，不光是可以减少plsql与sql引擎之间的频繁切换。还可以减少redo与undo的生成。--

首先创建测试表

create table test1 (c1 number , c2 number ,c3 number) ;
create table test2 (c1 number , c2 number ,c3 number) ;

开始测试

SQL> declare
2
2 l_stat_sql varchar2(2000) := 'select value from v$mystat ms, v$statname sn where ms.STATISTIC# = sn.STATISTIC# and name = :1 ';
3 type t is table of test2%rowtype ;
4 l t := t() ;
5 l_undo_stat1 int;
6 l_undo_stat2 int;
7 l_undo_stat3 int;
8 l_redo_stat1 int;
9 l_redo_stat2 int;
10 l_redo_stat3 int;
11 l_time_stat1 int;
12 l_time_stat2 int;
13 l_time_stat3 int;
14 begin
15 l_time_stat1 := dbms_utility.get_time ;
16 execute immediate l_stat_sql into l_redo_stat1 using 'redo size';
17 execute immediate l_stat_sql into l_undo_stat1 using 'undo change vector size';
18 for i in 1 .. 10000 loop
19 insert into test1 values(i,i/2,mod(i,2)) ;
20 end loop ;
21 l_time_stat2 := dbms_utility.get_time ;
22 execute immediate l_stat_sql into l_redo_stat2 using 'redo size';
23 execute immediate l_stat_sql into l_undo_stat2 using 'undo change vector size';
24 l.extend(10000) ;
25 for i in 1 .. 10000 loop
26 l(i).c1 := i ;
27 l(i).c2 := i/2 ;
28 l(i).c3 := mod(i,2) ;
29 end loop;
30 forall i in 1 .. l.last
31 insert into test2 values l(i) ;
32 l_time_stat3 := dbms_utility.get_time ;
33 execute immediate l_stat_sql into l_redo_stat3 using 'redo size';
34 execute immediate l_stat_sql into l_undo_stat3 using 'undo change vector size';
35
36 dbms_output.put_line('OneByOne redo : ' || (l_redo_stat2-l_redo_stat1) ) ;
37 dbms_output.put_line('Bulk redo : ' || (l_redo_stat3-l_redo_stat2) ) ;
38 dbms_output.put_line('-') ;
39 dbms_output.put_line('OneByOne undo : ' || (l_undo_stat2-l_undo_stat1) ) ;
40 dbms_output.put_line('Bulk undo : ' || (l_undo_stat3-l_undo_stat2) ) ;
41 dbms_output.put_line('-') ;
42 dbms_output.put_line('OneByOne time : ' || (l_time_stat2-l_time_stat1) ) ;
43 dbms_output.put_line('Bulk time : ' || (l_time_stat3-l_time_stat2) ) ;
44 end;
45 /

OneByOne redo : 2582244
Bulk redo : 228428
-
OneByOne undo : 681172
Bulk undo : 25432
-
OneByOne time : 84
Bulk time : 2

PL/SQL procedure successfully completed

--事实证明，使用bulk操作对比普通单条执行来说，不光是可以减少plsql与sql引擎之间的频繁切换。还可以减少redo与undo的生成。
--可以看到redo 相差10倍，undo相差将近20倍。
--时间上来说单条执行使用了840毫秒，，而批量模式则只使用了20毫秒，差距不可说不大。

因为实在同一个事务中，所以scn号相同

SQL> select ora_rowscn ,t.* from test1 t where rownum<=10 ;

ORA_ROWSCN C1 C2 C3
---------- ---------- ---------- ----------
17108596 2289 1144.5 1
17108596 2290 1145 0
17108596 2291 1145.5 1
17108596 2292 1146 0
17108596 2293 1146.5 1
17108596 2294 1147 0
17108596 2295 1147.5 1
17108596 2296 1148 0
17108596 2297 1148.5 1
17108596 2298 1149 0

10 rows selected

SQL> select ora_rowscn ,t.* from test2 t where rownum<=10 ;

ORA_ROWSCN C1 C2 C3
---------- ---------- ---------- ----------
17108596 2289 1144.5 1
17108596 2290 1145 0
17108596 2291 1145.5 1
17108596 2292 1146 0
17108596 2293 1146.5 1
17108596 2294 1147 0
17108596 2295 1147.5 1
17108596 2296 1148 0
17108596 2297 1148.5 1
17108596 2298 1149 0

10 rows selected