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

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

首先创建测试表

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