【转】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