如果不考虑事务的完整性，是不是在SQL中的COMMIT越多性能越好？

sundog315

原帖由 carcase 于 2010-8-27 12:27 发表
to  杨奇龙
多commit生成的commit 大于一起commit的量 这些多出来的redo开销是什么？

这个我跟踪了一些 不过偶不是很看的懂 呵呵
我贴出来 大家看一下  
第一条update 大家基本是一样的，都做同一件事情
change #1  分配回滚段
change #2  写镜像信息到回滚段
change #3 记录数据库的修改

从第二条update开始 就记录的redo就不一样了

1.分开commit的日志是这样的

REDO RECORD - Thread:1 RBA: 0x00028b.00000009.0144 LEN: 0x0060 VLD: 0x01
SCN: 0x0000.7c7c23c4 SUBSCN:  1 08/15/2010 11:04:54
CHANGE #1 TYP:0 CLS:21 AFN:2 DBA:0x00800029 OBJ:4294967295 SCN:0x0000.7c7c23c3 SEQ:  1 OP:5.4
ktucm redo: slt: 0x0013 sqn: 0x00001532 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x00806adc.08bb.20 ext: 2 spc: 2970 fbi: 0

REDO RECORD - Thread:1 RBA: 0x00028b.0000000a.0010 LEN: 0x0218 VLD: 0x05
SCN: 0x0000.7c7c23c6 SUBSCN:  5 08/15/2010 11:04:54
CHANGE #1 TYP:0 CLS:27 AFN:2 DBA:0x00800059 OBJ:4294967295 SCN:0x0000.7c7c2233 SEQ:  1 OP:5.2
ktudh redo: slt: 0x0013 sqn: 0x000013ce flg: 0x0012 siz: 176 fbi: 0
            uba: 0x008000af.0681.1e    pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:28 AFN:2 DBA:0x008000af OBJ:4294967295 SCN:0x0000.7c7c2232 SEQ:  2 OP:5.1
ktudb redo: siz: 176 spc: 3448 flg: 0x0012 seq: 0x0681 rec: 0x1e
            xid:  0x0006.013.000013ce  
ktubl redo: slt: 19 rci: 0 opc: 11.1 objn: 111826 objd: 111826 tsn: 4
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x008000af.0681.1c
prev ctl max cmt scn:  0x0000.7c7c049e  prev tx cmt scn:  0x0000.7c7c05b5
txn start scn:  0xffff.ffffffff  logon user: 55  prev brb: 8388777  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04  ver: 0x01  
op: L  itl: xid:  0x000a.022.0000102c uba: 0x00800312.05a6.06
                      flg: C---    lkc:  0     scn: 0x0000.7c7c2245
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000047  hdba: 0x01000043
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 8
ncol: 2 nnew: 1 size: 0
col  0: [ 2]  c1 06
CHANGE #3 TYP:2 CLS: 1 AFN:4 DBA:0x01000047 OBJ:111826 SCN:0x0000.7c7c23c4 SEQ:  1 OP:11.5
KTB Redo
op: 0x11  ver: 0x01  
op: F  xid:  0x0006.013.000013ce    uba: 0x008000af.0681.1e
Block cleanout record, scn:  0x0000.7c7c23c6 ver: 0x01 opt: 0x02, entries follow...
  itli: 2  flg: 2  scn: 0x0000.7c7c23c4
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000047  hdba: 0x01000043
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 8
ncol: 2 nnew: 1 size: 0
col  0: [ 2]  c1 07
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:5.20
session number   = 144
serial  number   = 7
transaction name =

跟第一次update是一样的，change1   到change 4 一步也不少的


一起commit的sql是这样的：
REDO RECORD - Thread:1 RBA: 0x00028b.0000000f.0038 LEN: 0x010c VLD: 0x01
SCN: 0x0000.7c7c23cc SUBSCN:  5 08/15/2010 11:04:55
CHANGE #1 TYP:0 CLS:18 AFN:2 DBA:0x00807147 OBJ:4294967295 SCN:0x0000.7c7c23cc SEQ:  1 OP:5.1
ktudb redo: siz: 112 spc: 3008 flg: 0x0022 seq: 0x070f rec: 0x1e
            xid:  0x0001.01a.00000fe4  
ktubu redo: slt: 26 rci: 29 opc: 11.1 objn: 113006 objd: 113006 tsn: 4
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01  
op: C  uba: 0x00807147.070f.1d
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x02000be4  hdba: 0x02000be3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 8
ncol: 2 nnew: 1 size: 0
col  0: [ 2]  c1 0f
CHANGE #2 TYP:0 CLS: 1 AFN:8 DBA:0x02000be4 OBJ:113006 SCN:0x0000.7c7c23cc SEQ:  1 OP:11.5
KTB Redo
op: 0x02  ver: 0x01  
op: C  uba: 0x00807147.070f.1e
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x02000be4  hdba: 0x02000be3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 2 ckix: 8
ncol: 2 nnew: 1 size: 0
col  0: [ 2]  c1 06

REDO RECORD - Thread:1 RBA: 0x00028b.0000000f.0144 LEN: 0x010c VLD: 0x01
SCN: 0x0000.7c7c23cd SUBSCN:  1 08/15/2010 11:04:55
CHANGE #1 TYP:0 CLS:18 AFN:2 DBA:0x00807147 OBJ:4294967295 SCN:0x0000.7c7c23cc SEQ:  2 OP:5.1
ktudb redo: siz: 112 spc: 2894 flg: 0x0022 seq: 0x070f rec: 0x1f
            xid:  0x0001.01a.00000fe4  
ktubu redo: slt: 26 rci: 30 opc: 11.1 objn: 113006 objd: 113006 tsn: 4
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01  
op: C  uba: 0x00807147.070f.1e
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x02000be4  hdba: 0x02000be3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 8
ncol: 2 nnew: 1 size: 0
col  0: [ 2]  c1 06
CHANGE #2 TYP:0 CLS: 1 AFN:8 DBA:0x02000be4 OBJ:113006 SCN:0x0000.7c7c23cc SEQ:  2 OP:11.5
KTB Redo
op: 0x02  ver: 0x01  
op: C  uba: 0x00807147.070f.1f
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x02000be4  hdba: 0x02000be3
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 8
ncol: 2 nnew: 1 size: 0
col  0: [ 2]  c1 07

他就只有两部 change1 和chang2  （没有change4 ，那是因为还没有commit)
但是相比之下，还是少了一个步骤，
我看了一下  应该是少了  把前镜像记录写到undo的这一步没有了，或者这一步的信息很少
应该是 update同一张表的缘故，前镜像取自前一个update了，所有记录的redo少了

高手解释一下

这个可以参见redo internal.ppt文档。里面写的比较清楚。