oracle undo系列(二)
 本文测试多次dml未提交select的一致性读的实现过程

--连续update2次后select

-----会话1
SQL> create table t_undo(a int);
 
Table created
 
SQL> insert into t_undo values(1);
 
1 row inserted
 
SQL> commit;
 
Commit complete

---会话2
SQL> update t_undo set a=2;

1 row updated.

SQL> update t_undo set a=3;

1 row updated.

---会话1
SQL> alter session set events '10046 trace name context forever,level 12';
 
Session altered
 
SQL> select * from t_undo;
 
                                      A
---------------------------------------
                                      1
 
SQL> alter session set events '10046 trace name context off';
 
Session altered

--查看trace文件
WAIT #10: nam='db file sequential read' ela= 16705 file#=10 block#=276619 blocks=1 obj#=69927 tim=19271072898

---经查询即t_undo表
SQL> select object_name from dba_objects where object_id=69927;
 
OBJECT_NAME
--------------------------------------------------------------------------------
T_UNDO
 
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t_undo;
 
DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------ ------------------------------
                            10                         276619
                           
--dump上述的数据块
alter session dump datafile 10 dump  block 276619;   

---trace内容如下

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x003e.008.0000007c  0x00c0422e.0127.29  C---    0  scn 0x0000.00874bc4
0x02   0x0023.000.0000007c  0x00c1adac.011f.0c  ----    1  fsc 0x0000.00000000  

-------注意：先把itl的uba第一部分转换为10进制
SQL> select to_number('00c1adac','XXXXXXXXXXXXXXX') from dual
  2  ;
 
TO_NUMBER('00C04173','XXXXXXXX
------------------------------
                      12692908
--然后再把上述的值用dbms_utility转换为撤消表空间的文件号及数据块号,最后再dump
SQL> select dbms_utility.data_block_address_file(12692908), dbms_utility.data_block_address_block(12692908) from dual;
 
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
                             3                          109996
                            
--dump撤消数据块
--根据itl的uba第三部分0x00c1adac.011f.0c直接查询0c的撤消数据块撤消条目记录号（即对应rec:0xc）
----
*-----------------------------
* Rec #0xc  slt: 0x00  objn: 69927(0x00011127)  objd: 69927  tblspc: 8(0x00000008)
*       Layer:  11 (Row)   opc: 1   rci 0x0b  
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
compat bit: 4 (post-11) padding: 0
op: C  uba: 0x00c1adac.011f.0b ---------------------就这一行
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 1 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0284388b  hdba: 0x0284388a
itli: 2  ispac: 0  maxfr: 4858
vect = 0
col  0: [ 2]  c1 03 ,经换算为第一次更新的2，而非未更新前的1

--此时还是未找到update之前的值1，大家往上看,op: C  uba: 0x00c1adac.011f.0b找到0b的撤消记录条目
---另一个问题:oracle怎么知道还要往上回朔找上级的撤消块条目呢，主要看如果撤消记录条目有op: C说明还要往上回朔
--如果op:Z则不用再找了，至此为止

----如下是oxb的撤消记录条目
*-----------------------------
* Rec #0xb  slt: 0x00  objn: 69927(0x00011127)  objd: 69927  tblspc: 8(0x00000008)
*       Layer:  11 (Row)   opc: 1   rci 0x00  
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c1adac.011f.04 ctl max scn: 0x0000.00864a2b prv tx scn: 0x0000.00864a71
txn start scn: scn: 0x0000.00874b91 logon user: 61
 prev brb: 12718743 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01 
compat bit: 4 (post-11) padding: 0
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0284388b  hdba: 0x0284388a
itli: 2  ispac: 0  maxfr: 4858
vect = 0
col  0: [ 2]  c1 02      ---这就是所有update之前的前镜像的1值

至此oracle成功完成了多次update操作后的一致性读操作,可见一致性读要读取多个数据块；相当花费资源
一致性读对于undo的竞争会很严重在高并发情况下