oracle undo系列(二)

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的競爭會很嚴重在高併發情況下