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

最新推荐文章于 2022-10-03 15:58:36 发布

renfengjun

最新推荐文章于 2022-10-03 15:58:36 发布

阅读量4.7k

点赞数 2

分类专栏： OraclePLSQL Oracle

本文链接：https://blog.csdn.net/renfengjun/article/details/8334733

版权

Oracle 同时被 2 个专栏收录

104 篇文章 1 订阅

订阅专栏

OraclePLSQL

8 篇文章 0 订阅

订阅专栏

首先创建测试表

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

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 17108590
scn 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:0
KTB Redo 
op: 0x02  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c01760.00f1.01
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100e27d  hdba: 0x0100e262
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 202(0xca) size/delt: 15
fb: --H-FL-- lb: 0x1  cc: 3
null: ---
col  0: [ 3]  c2 62 38
col  1: [ 4]  c2 31 4e 33
col  2: [ 2]  c1 02



CHANGE #2 TYP:0 CLS:1 AFN:4 DBA:0x0100e27d OBJ:80823 SCN:0x0000.01050e70 SEQ:12 OP:11.2 ENC:0 RBL:0
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c01762.00f1.0d
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100e27d  hdba: 0x0100e262
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 446(0x1be) size/delt: 15
fb: --H-FL-- lb: 0x1  cc: 3
null: ---
col  0: [ 3]  c2 64 64
col  1: [ 4]  c2 32 64 33
col  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:0
KTB Redo
op: 0x01  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x0002.00b.00000669    uba: 0x00c01762.00f1.0f
KDO Op code: QMI row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100e283  hdba: 0x0100eeea
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 lock: 1 nrow: 255
slot[0]: 0
tl: 12 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 02
col  1: [ 2]  c0 33
col  2: [ 2]  c1 02
slot[1]: 1
tl: 11 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 03
col  1: [ 2]  c1 02
col  2: [ 1]  80
slot[2]: 2
tl: 13 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 04
col  1: [ 3]  c1 02 33
col  2: [ 2]  c1 02
slot[3]: 3
tl: 11 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 05
col  1: [ 2]  c1 03
col  2: [ 1]  80
....
slot[254]: 254
tl: 15 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 3]  c2 03 38
col  1: [ 4]  c2 02 1c 33
col  2: [ 2]  c1 02

至此，验证结束。

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

参考资料：

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

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

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

renfengjun

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
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 valu
复制链接

扫一扫