当前位置: 代码迷 >> SQL >> 【转】PLSQL批量Forall可操作性能提升详解
  详细解决方案

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

热度:26   发布时间:2016-05-05 12:09:57.0
【转】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
  相关解决方案