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

通常在SQL语句中给PL/SQL变量赋值叫做绑定（Binding）,一次绑定一个完整的集合称为批量绑定（Bulk Binding）。

批量绑定（Bulk binds）可以通过减少在PL/SQL和SQL引擎之间的上下文切换(context switches )提高了性能.

批量绑定（Bulk binds）包括：
(i) Input collections, use the FORALL statement，一般用来改善DML（INSERT、UPDATE和DELETE) 操作的性能
(ii) Output collections, use BULK COLLECT clause，一般用来提高查询（SELECT）的性能


首先创建测试表

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

开始测试

SQL>
DECLARE
l_stat_sql VARCHAR2(2000) := 'select value from v$mystat ms, v$statname sn where ms.STATISTIC# = sn.STATISTIC# and name = :1 ';
TYPE t IS TABLE OF test2%ROWTYPE;
l t := t();
l_undo_stat1 INT;
l_undo_stat2 INT;
l_undo_stat3 INT;
l_redo_stat1 INT;
l_redo_stat2 INT;
l_redo_stat3 INT;
l_time_stat1 INT;
l_time_stat2 INT;
l_time_stat3 INT;
BEGIN
l_time_stat1 := dbms_utility.get_time;
EXECUTE IMMEDIATE l_stat_sql
INTO l_redo_stat1
USING 'redo size';
EXECUTE IMMEDIATE l_stat_sql
INTO l_undo_stat1
USING 'undo change vector size';
FOR i IN 1 .. 10000 LOOP
INSERT INTO test1
VALUES
(i,
i / 2,
MOD(i, 2));
END LOOP;
l_time_stat2 := dbms_utility.get_time;
EXECUTE IMMEDIATE l_stat_sql
INTO l_redo_stat2
USING 'redo size';
EXECUTE IMMEDIATE l_stat_sql
INTO l_undo_stat2
USING 'undo change vector size';
l.EXTEND(10000);
FOR i IN 1 .. 10000 LOOP
l(i).c1 := i;
l(i).c2 := i / 2;
l(i).c3 := MOD(i, 2);
END LOOP;
FORALL i IN 1 .. l.LAST
INSERT INTO test2 VALUES l (i);
l_time_stat3 := dbms_utility.get_time;
EXECUTE IMMEDIATE l_stat_sql
INTO l_redo_stat3
USING 'redo size';
EXECUTE IMMEDIATE l_stat_sql
INTO l_undo_stat3
USING 'undo change vector size';

dbms_output.put_line('OneByOne redo : ' ||
(l_redo_stat2 - l_redo_stat1));
dbms_output.put_line('Bulk redo : ' ||
(l_redo_stat3 - l_redo_stat2));
dbms_output.put_line('-');
dbms_output.put_line('OneByOne undo : ' ||
(l_undo_stat2 - l_undo_stat1));
dbms_output.put_line('Bulk undo : ' ||
(l_undo_stat3 - l_undo_stat2));
dbms_output.put_line('-');
dbms_output.put_line('OneByOne time : ' ||
(l_time_stat2 - l_time_stat1));
dbms_output.put_line('Bulk time : ' ||
(l_time_stat3 - l_time_stat2));
END;/


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