【话题讨论】深入解析Oracle IMU模式下的REDO格式

guoyJoe

rebuild 发表于 2014-1-3 14:26
开启IMU必须禁用supplemental log，如果查v$database里面SUPPLEMENTAL_LOG_DATA_PL为YES怎么设置为NO?

Oracle11g禁用了supplemental logging
要开启如下命令：
alter database add supplemental log data;

guoyJoe

hello985 发表于 2014-1-3 14:40
imu 最关键是减少dml对undo的高并发放，提高i/o使用率

这不是主要原因吧？一起讨探一下....

guoyJoe

traveldba 发表于 2014-1-3 14:42
3.在private redo中生成数据块的redo change vector
4.apply redo change vector 到 数据块
5.apply un ...

你错了，以实验证明：
gyj@OCM> UPDATE GYJ_TEST SET NAME='AAAAAAAAAA' WHERE ID=1;（XXXXX-58 58 58 58 58）  A=>41

1 row updated.

gyj@OCM> UPDATE GYJ_TEST SET NAME='BBBBBBBBBB' WHERE ID=2; (YYYYY-59 59 59 59 59)   B=>42

1 row updated.

gyj@OCM> COMMIT;

Commit complete.


一条记日志==》6个CHANGE VECTOR
CHANGE #1 ==>OP:11.5 -->UPDATE 41 41 41 41 41 41 41 41 41 41  AAAAAAAAAA
CHANGE #2 ==>OP:5.2 -->开始一个事务
CHANGE #3 ==>OP:11.5 —>UPDATE42 42 42 42 42 42 42 42 42 42    BBBBBBBBBB
CHANGE #4 ==>OP:5.4  --->COMMIT
CHANGE #5 ==>OP:5.1  --->UNDO   58 58 58 58 58  XXXXX
CHANGE #6 ==>OP:5.1  --->UNDO    59 59 59 59 59 YYYYY

guoyJoe

traveldba 发表于 2014-1-3 14:42
3.在private redo中生成数据块的redo change vector
4.apply redo change vector 到 数据块
5.apply un ...

1. REDO RECORD - Thread:1 RBA: 0x000056.00000010.0010 LEN: 0x0364 VLD: 0x0d
   
2. SCN: 0x0000.001d83b0 SUBSCN:  1 01/07/2014 20:48:12
   
3. (LWN RBA: 0x000056.00000010.0010 LEN: 0002 NST: 0001 SCN: 0x0000.001d83af)
   
4. 
   
5. CHANGE #1 TYP:2 CLS:1 AFN:6 DBA:0x01800087 OBJ:76869 SCN:0x0000.001d838e SEQ:2 OP:11.5 ENC:0 RBL:0
   
6. KTB Redo
   
7. op: 0x11  ver: 0x01
   
8. compat bit: 4 (post-11) padding: 1
   
9. op: F  xid:  0x0006.013.00000541    uba: 0x00c00411.0113.1b
   
10. Block cleanout record, scn:  0x0000.001d83aa ver: 0x01 opt: 0x02, entries follow...
    
11.   itli: 1  flg: 2  scn: 0x0000.001d838e
    
12. KDO Op code: URP row dependencies Disabled
    
13.   xtype: XA flags: 0x00000000  bdba: 0x01800087  hdba: 0x01800082
    
14. itli: 2  ispac: 0  maxfr: 4858
    
15. tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
    
16. ncol: 2 nnew: 1 size: 5
    
17. col  1: [10]  41 41 41 41 41 41 41 41 41 41
    
18. 
    
19. CHANGE #2 TYP:0 CLS:27 AFN:3 DBA:0x00c000d0 OBJ:4294967295 SCN:0x0000.001d837c SEQ:1 OP:5.2 ENC:0 RBL:0
    
20. ktudh redo: slt: 0x0013 sqn: 0x00000541 flg: 0x0012 siz: 164 fbi: 0
    
21.             uba: 0x00c00411.0113.1b    pxid:  0x0000.000.00000000
    
22.             
    
23. CHANGE #3 TYP:2 CLS:1 AFN:6 DBA:0x01800084 OBJ:76869 SCN:0x0000.001d838e SEQ:2 OP:11.5 ENC:0 RBL:0
    
24. KTB Redo
    
25. op: 0x11  ver: 0x01
    
26. compat bit: 4 (post-11) padding: 1
    
27. op: F  xid:  0x0006.013.00000541    uba: 0x00c00411.0113.1c
    
28. Block cleanout record, scn:  0x0000.001d83ad ver: 0x01 opt: 0x02, entries follow...
    
29.   itli: 2  flg: 2  scn: 0x0000.001d838e
    
30. KDO Op code: URP row dependencies Disabled
    
31.   xtype: XA flags: 0x00000000  bdba: 0x01800084  hdba: 0x01800082
    
32. itli: 1  ispac: 0  maxfr: 4858
    
33. tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 0
    
34. ncol: 2 nnew: 1 size: 5
    
35. col  1: [10]  42 42 42 42 42 42 42 42 42 42
    
36. 
    
37. CHANGE #4 TYP:0 CLS:27 AFN:3 DBA:0x00c000d0 OBJ:4294967295 SCN:0x0000.001d83b0 SEQ:1 OP:5.4 ENC:0 RBL:0
    
38. ktucm redo: slt: 0x0013 sqn: 0x00000541 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c00411.0113.1c ext: 2 spc: 4510 fbi: 0
    
39. 
    
40. CHANGE #5 TYP:0 CLS:28 AFN:3 DBA:0x00c00411 OBJ:4294967295 SCN:0x0000.001d837b SEQ:1 OP:5.1 ENC:0 RBL:0
    
41. ktudb redo: siz: 164 spc: 4790 flg: 0x0012 seq: 0x0113 rec: 0x1b
    
42.             xid:  0x0006.013.00000541
    
43. ktubl redo: slt: 19 rci: 0 opc: 11.1 [objn: 76869 objd: 76869 tsn: 7]
    
44. Undo type:  Regular undo        Begin trans    Last buffer split:  No
    
45. Temp Object:  No
    
46. Tablespace Undo:  No
    
47.              0x00000000  prev ctl uba: 0x00c00411.0113.1a
    
48. prev ctl max cmt scn:  0x0000.001d7dfd  prev tx cmt scn:  0x0000.001d7e05
    
49. txn start scn:  0x0000.001d839e  logon user: 90  prev brb: 12583963  prev bcl: 0 BuExt idx: 0 flg2: 0
    
50. KDO undo record:
    
51. KTB Redo
    
52. op: 0x04  ver: 0x01
    
53. compat bit: 4 (post-11) padding: 1
    
54. op: L  itl: xid:  0x0009.013.00000477 uba: 0x00c00b53.011f.20
    
55.                       flg: C---    lkc:  0     scn: 0x0000.001d8153
    
56. KDO Op code: URP row dependencies Disabled
    
57.   xtype: XA flags: 0x00000000  bdba: 0x01800087  hdba: 0x01800082
    
58. itli: 2  ispac: 0  maxfr: 4858
    
59. tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
    
60. ncol: 2 nnew: 1 size: -5
    
61. col  1: [ 5]  58 58 58 58 58
    
62. 
    
63. CHANGE #6 TYP:0 CLS:28 AFN:3 DBA:0x00c00411 OBJ:4294967295 SCN:0x0000.001d83b0 SEQ:1 OP:5.1 ENC:0 RBL:0
    
64. ktudb redo: siz: 112 spc: 4624 flg: 0x0022 seq: 0x0113 rec: 0x1c
    
65.             xid:  0x0006.013.00000541
    
66. ktubu redo: slt: 19 rci: 27 opc: 11.1 objn: 76869 objd: 76869 tsn: 7
    
67. Undo type:  Regular undo       Undo type:  Last buffer split:  No
    
68. Tablespace Undo:  No
    
69.              0x00000000
    
70. KDO undo record:
    
71. KTB Redo
    
72. op: 0x04  ver: 0x01
    
73. compat bit: 4 (post-11) padding: 1
    
74. op: L  itl: xid:  0x0009.013.00000477 uba: 0x00c00b53.011f.21
    
75.                       flg: C---    lkc:  0     scn: 0x0000.001d8153
    
76. KDO Op code: URP row dependencies Disabled
    
77.   xtype: XA flags: 0x00000000  bdba: 0x01800084  hdba: 0x01800082
    
78. itli: 1  ispac: 0  maxfr: 4858
    
79. tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
    
80. ncol: 2 nnew: 1 size: -5
    
81. col  1: [ 5]  59 59 59 59 59

复制代码

guoyJoe

遗忘的deny 发表于 2014-1-3 15:24
又一高深贴。看到5.1，5.2这些就怕

不用怕，一些OP不需要纠结。

guoyJoe

miniid1 发表于 2014-1-5 13:20
加上了IMU就和以前不一样。 IMU引入的另一个目的是：
以前每次更改数据块，Oracle要立即去更新undo bloc ...

说的很好，很强大！

guoyJoe

miniid1 发表于 2014-1-5 13:20
加上了IMU就和以前不一样。 IMU引入的另一个目的是：
以前每次更改数据块，Oracle要立即去更新undo bloc ...

非IMU下，在一个事务下面分别做两条UPDATE

gyj@OCM> UPDATE GYJ_TEST SET NAME='XXXXX' WHERE ID=1;

1 row updated.

gyj@OCM> UPDATE GYJ_TEST SET NAME='YYYYY' WHERE ID=2;

1 row updated.

gyj@OCM> COMMIT;

Commit complete.

三条日志：
第一条日志：
   日志头
   OP=5.2 ==>事务开始了
   OP=5.1 ==>把数据前的值放到UNDO块
   OP=11.5 ==>update修改数据
   
第二条日志：
    日志头
    OP=5.1 ==>把数据前的值放到UNDO块
    OP=11.5 ==>update修改数据
  
第三条日志：
     日志头
     OP=5.4 ==>提交（事务结束）

guoyJoe

miniid1 发表于 2014-1-5 13:20
加上了IMU就和以前不一样。 IMU引入的另一个目的是：
以前每次更改数据块，Oracle要立即去更新undo bloc ...

REDO RECORD - Thread:1 RBA: 0x000054.000012e6.0010 LEN: 0x01f0 VLD: 0x05
SCN: 0x0000.001d7e7b SUBSCN:  1 01/07/2014 20:07:01
(LWN RBA: 0x000054.000012e6.0010 LEN: 0001 NST: 0001 SCN: 0x0000.001d7e7a)
CHANGE #1 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0000.001d7e4a SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0005 sqn: 0x0000047f flg: 0x0012 siz: 168 fbi: 0
            uba: 0x00c00337.00ee.3d    pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:32 AFN:3 DBA:0x00c00337 OBJ:4294967295 SCN:0x0000.001d7e49 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 168 spc: 486 flg: 0x0012 seq: 0x00ee rec: 0x3d
            xid:  0x0008.005.0000047f
ktubl redo: slt: 5 rci: 0 opc: 11.1 [objn: 76869 objd: 76869 tsn: 7]
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00c00337.00ee.3a
prev ctl max cmt scn:  0x0000.001d7815  prev tx cmt scn:  0x0000.001d782c
txn start scn:  0xffff.ffffffff  logon user: 90  prev brb: 12583732  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0006.009.00000536 uba: 0x00c06f2d.010f.04
                      flg: C---    lkc:  0     scn: 0x0000.001cf5cd
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01800087  hdba: 0x01800082
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 5
col  1: [10]  42 42 42 42 42 42 42 42 42 42
CHANGE #3 TYP:2 CLS:1 AFN:6 DBA:0x01800087 OBJ:76869 SCN:0x0000.001cfc69 SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x0008.005.0000047f    uba: 0x00c00337.00ee.3d
Block cleanout record, scn:  0x0000.001d7e7b ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0000.001cf5cd
  itli: 2  flg: 2  scn: 0x0000.001cfc69
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01800087  hdba: 0x01800082
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 0
ncol: 2 nnew: 1 size: -5
col  1: [ 5]  58 58 58 58 58






REDO RECORD - Thread:1 RBA: 0x000054.000012e7.0010 LEN: 0x0178 VLD: 0x05
SCN: 0x0000.001d7e81 SUBSCN:  1 01/07/2014 20:07:16
(LWN RBA: 0x000054.000012e7.0010 LEN: 0001 NST: 0001 SCN: 0x0000.001d7e80)
CHANGE #1 TYP:0 CLS:32 AFN:3 DBA:0x00c00337 OBJ:4294967295 SCN:0x0000.001d7e7b SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 116 spc: 316 flg: 0x0022 seq: 0x00ee rec: 0x3e
            xid:  0x0008.005.0000047f
ktubu redo: slt: 5 rci: 61 opc: 11.1 objn: 76869 objd: 76869 tsn: 7
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0007.018.00000312 uba: 0x00c00a1d.00a2.1f
                      flg: C---    lkc:  0     scn: 0x0000.0016bb7f
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01800084  hdba: 0x01800082
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 5
col  1: [10]  42 42 42 42 42 42 42 42 42 42
CHANGE #2 TYP:2 CLS:1 AFN:6 DBA:0x01800084 OBJ:76869 SCN:0x0000.001cfc69 SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x0008.005.0000047f    uba: 0x00c00337.00ee.3e
Block cleanout record, scn:  0x0000.001d7e81 ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0000.001cfc69
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01800084  hdba: 0x01800082
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 2 nnew: 1 size: -5
col  1: [ 5]  59 59 59 59 59

REDO RECORD - Thread:1 RBA: 0x000054.000012e8.0010 LEN: 0x007c VLD: 0x05
SCN: 0x0000.001d7e83 SUBSCN:  1 01/07/2014 20:07:17
(LWN RBA: 0x000054.000012e8.0010 LEN: 0001 NST: 0001 SCN: 0x0000.001d7e82)
CHANGE #1 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0000.001d7e7b SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0005 sqn: 0x0000047f srt: 0 sta: 9 flg: 0x0

guoyJoe

mikeliuy 发表于 2014-1-5 13:23
先顶一下!

mikeliuy大师，一起讨论！

guoyJoe

miniid1 发表于 2014-1-5 13:23
JL的Oracle core上说只有提交时才apply redo到data block, undo 到 undo block，但实际情况好像不是这样，r ...

没看懂你这语句？请详细说明？