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 : 2582244Bulk redo     : 228428-OneByOne undo : 681172Bulk undo     : 25432-OneByOne time : 84Bulk 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

ora_rowscn :
For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row in the current session.

每次更改数据commit后ora_rowscn都会改变。针对block级别，而不是row。这只是取个大概值。

向前推进一些即可看到相关的redo信息。

alter system dump logfile '/u03/apps/oracle/oradata/osdt/redo02.log'scn min 17108590scn max 17108596 ;

普通操作:一次只能包含一条新增数据的更改

Opcode 11.2 : Insert Row Piece

CHANGE #3 TYP:0 CLS:1 AFN:4 DBA:0x0100e27d OBJ:80823 SCN:0x0000.01050e6d SEQ:116 OP:11.2 ENC:0 RBL:0KTB Redo op: 0x02  ver: 0x01  compat bit: 4 (post-11) padding: 1op: C  uba: 0x00c01760.00f1.01KDO Op code: IRP row dependencies Disabled  xtype: XA flags: 0x00000000  bdba: 0x0100e27d  hdba: 0x0100e262itli: 1  ispac: 0  maxfr: 4858tabn: 0 slot: 202(0xca) size/delt: 15fb: --H-FL-- lb: 0x1  cc: 3null: ---col  0: [ 3]  c2 62 38col  1: [ 4]  c2 31 4e 33col  2: [ 2]  c1 02CHANGE #2 TYP:0 CLS:1 AFN:4 DBA:0x0100e27d OBJ:80823 SCN:0x0000.01050e70 SEQ:12 OP:11.2 ENC:0 RBL:0KTB Redoop: 0x02  ver: 0x01compat bit: 4 (post-11) padding: 1op: C  uba: 0x00c01762.00f1.0dKDO Op code: IRP row dependencies Disabled  xtype: XA flags: 0x00000000  bdba: 0x0100e27d  hdba: 0x0100e262itli: 1  ispac: 0  maxfr: 4858tabn: 0 slot: 446(0x1be) size/delt: 15fb: --H-FL-- lb: 0x1  cc: 3null: ---col  0: [ 3]  c2 64 64col  1: [ 4]  c2 32 64 33col  2: [ 2]  c1 02

bulk操作：quick multi-insert 可以使一个change vector 中包含更多的data change。

Opcode 11.11 :Quick Multi-Insert

CHANGE #2 TYP:0 CLS:1 AFN:4 DBA:0x0100e283 OBJ:80824 SCN:0x0000.01050dc3 SEQ:3 OP:11.11 ENC:0 RBL:0KTB Redoop: 0x01  ver: 0x01  compat bit: 4 (post-11) padding: 1op: F  xid:  0x0002.00b.00000669    uba: 0x00c01762.00f1.0fKDO Op code: QMI row dependencies Disabled  xtype: XA flags: 0x00000000  bdba: 0x0100e283  hdba: 0x0100eeeaitli: 1  ispac: 0  maxfr: 4858tabn: 0 lock: 1 nrow: 255slot[0]: 0tl: 12 fb: --H-FL-- lb: 0x0  cc: 3col  0: [ 2]  c1 02col  1: [ 2]  c0 33col  2: [ 2]  c1 02slot[1]: 1tl: 11 fb: --H-FL-- lb: 0x0  cc: 3col  0: [ 2]  c1 03col  1: [ 2]  c1 02col  2: [ 1]  80slot[2]: 2tl: 13 fb: --H-FL-- lb: 0x0  cc: 3col  0: [ 2]  c1 04col  1: [ 3]  c1 02 33col  2: [ 2]  c1 02slot[3]: 3tl: 11 fb: --H-FL-- lb: 0x0  cc: 3col  0: [ 2]  c1 05col  1: [ 2]  c1 03col  2: [ 1]  80....slot[254]: 254tl: 15 fb: --H-FL-- lb: 0x0  cc: 3col  0: [ 3]  c2 03 38col  1: [ 4]  c2 02 1c 33col  2: [ 2]  c1 02

至此，验证结束。

（delete和insert都可以从forall上面得到巨大的性能提升。但是对于update来说opcode没有相关操作，提升应该不会那么明显）

参考资料：

DBA的思想天空:感悟Oracle数据库本质白鳝

Oracle PL\SQL实战 Adrian Billington , Martin Büchi 等，翻译者卢涛（〇〇总知道是谁吧）

Oracle PL/SQL最佳实践 Steven Feuerstein ，翻译者张平, 潘显俊