oracle undo系列(三)
oracle undo系列(三)
發生事務時，會先在資料塊上分配一個itl條目;於是產生了本文：
我想到幾個問題：
1,更新同一個表的同一條記錄多次，會佔用一條還是多條itl條目
2,更新同一個表的不同記錄，是否會對應多條itl條目
先看第1個問題：
---會話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.
SQL> update t_undo set a=4;
1 row updated.
---會話1（注：如看不到單塊讀：alter system flush buffer_cache）
WAIT #6: nam='db file sequential read' ela= 571 file#=10 block#=276618 blocks=1 obj#=69928 tim=26973873653
WAIT #6: nam='db file scattered read' ela= 468 file#=10 block#=276619 blocks=5 obj#=69928 tim=26973874509
*** 2013-03-21 16:47:25.979
WAIT #6: nam='db file sequential read' ela= 126477 file#=3 block#=2656 blocks=1 obj#=0 tim=26974001159
WAIT #6: nam='db file sequential read' ela= 450 file#=3 block#=113578 blocks=1 obj#=0 tim=26974001933
--會話1
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
--會話1,276618為段頭塊,由上trace可知先讀段頭，後讀資料塊；
SQL> select header_file,header_block from dba_segments where segment_name='T_UNDO';
HEADER_FILE HEADER_BLOCK
----------- ------------
10 276618
--會話1：依次讀了段頭塊和資料塊後，還分別單塊讀了3號檔案的2個塊,我們看看2兩個塊
---可知2656塊是回滾段頭塊
scn: 0x0000.008a5e9b seq: 0x01 flg: 0x04 tail: 0x5e9b2601
frmt: 0x02 chkval: 0x5529 type: 0x26=KTU SMU HEADER BLOCK
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 13 #blocks: 1423
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00c1bbaa ext#: 7 blk#: 42 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 7
Unlocked
Map Header:: next 0x00000000 #extents: 13 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00c00a61 length: 7
0x00c004d0 length: 8
--中間略
0x00c0ff00 length: 128
0x00c14500 length: 128
0x00c18700 length: 128
0x00c19480 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1363853239
Extent Number:1 Commit Time: 1363853239
--中間略
Extent Number:11 Commit Time: 1363853239
Extent Number:12 Commit Time: 1363853239
TRN CTL:: seq: 0x0136 chd: 0x000b ctl: 0x0019 inc: 0x00000000 nfb: 0x0000
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c1bbaa.0136.4e scn: 0x0000.0088ddf2
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.0136.4d ext: 0x7 spc: 0x98c
uba: 0x00000000.0120.02 ext: 0x4 spc: 0x1f0e
uba: 0x00000000.0120.01 ext: 0x4 spc: 0x1eb0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x007d 0x001d 0x0000.008a0bfc 0x00c09e87 0x0000.000.00000000 0x00000001 0x00000000 1363853920
0x01 9 0x00 0x007d 0x000d 0x0000.008937ab 0x00c1453a 0x0000.000.00000000 0x00000001 0x00000000 1363853099
0x02 9 0x00 0x007b 0x000a 0x0000.0089ba23 0x00c0d342 0x0000.000.00000000 0x00000052 0x00000000 1363853762
中間略
0x21 9 0x00 0x007e 0x0011 0x0000.008a5735 0x00c1bbaa 0x0000.000.00000000 0x00000001 0x00000000 1363854058
EXT TRN CTL::
usn: 23
sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
sp5:0x00000000 sp6:0x00000000 sp7:0x00000000 sp8:0x00000000
EXT TRN TBL::
index extflag extHash extSpare1 extSpare2
---------------------------------------------------
0x00 0x00000000 0x00000000 0x00000000 0x00000000
0x01 0x00000000 0x00000000 0x00000000 0x00000000
中間略
0x21 0x00000000 0x00000000 0x00000000 0x00000000
End dump data blocks tsn: 2 file#: 3 minblk 2656 maxblk 2656
--dump另一個113578資料塊,可知是具體的撤消資料塊
scn: 0x0000.008a5e9e seq: 0x01 flg: 0x04 tail: 0x5e9e0201
frmt: 0x02 chkval: 0x1e54 type: 0x02=KTU UNDO BLOCK
--最新的撤消記錄條目，轉換為3,而不是4;4的撤消記錄條目呢
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: C uba: 0x00c1bbaa.0136.4f
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 04
--我們再dump下表的資料塊
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000e.010.000000b0 0x00c1c212.017b.3f C--- 0 scn 0x0000.008a5e94
0x02 0x0017.014.0000007e 0x00c1bbaa.0136.50 ---- 1 fsc 0x0000.00000000
SQL> select to_number('00c1bbaa','XXXXXXXXXXXXXXX') from dual
2 ;
TO_NUMBER('00C04173','XXXXXXXX
------------------------------
12696490
--然後再把上述的值用dbms_utility轉換為撤消表空間的檔案號及資料塊號,最後再dump
SQL> select dbms_utility.data_block_address_file(12696490), dbms_utility.data_block_address_block(12696490) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
3 113578 --即本文開頭10046 trace單塊讀的塊，undo block
--再dump下上述的itl的uba ,根據itl直接定位到如下撤消記錄條目
* Rec #0x50 slt: 0x14 objn: 69928(0x00011128) objd: 69929 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x4f
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: 0x00c1bbaa.0136.4f
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 04 即找到前映象
小結；多個事務對一個表同條記錄dml，僅佔用一個itl條目
--再看第2個問題：更新同一個表的不同記錄，是否會對應多條itl條目
--準備測試環境略：基於上述增加一條記錄
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0024.017.00000083 0x00c0f485.013c.0a --U- 1 fsc 0x0000.008a69bf
0x02 0x0011.018.000000f6 0x00c1b70b.0199.08 --U- 1 fsc 0x0000.008a6802
小結：更新同一個表不同記錄會佔用多條itl條目
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-756738/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle undo系列(二)Oracle
- oracle redo和undo系列一Oracle Redo
- Oracle Redo and UndoOracle Redo
- Oracle undo 管理Oracle
- Oracle Undo SegmentOracle
- oracle undo管理Oracle
- oracle undo一Oracle
- oracle undo segment header 事務表transaction table系列一OracleHeader
- Oracle深入Undo探究Oracle
- Oracle UNDO引數Oracle
- oracle undo 使用分析Oracle
- Oracle In Memory Undo(IMU)Oracle
- Oracle Undo 的配置Oracle
- Oracle Undo的作用Oracle
- Oracle In Memory Undo(轉)Oracle
- oracle v$lock系列之三Oracle
- Oracle OCP(48)：UNDO TABLESPACEOracle
- oracle undo分配規則Oracle
- oracle undo 表空間Oracle
- Oracle undo管理詳解Oracle
- Oracle Undo的學習Oracle
- oracle的undo的作用Oracle
- oracle 11g不同會話產生的事務會使用相同的undo segment嗎--undo系列之一Oracle會話
- oracle 11g bootstrap$系列三Oracleboot
- InnoDB文件筆記（三）—— Undo Log筆記
- Oracle 12c 新特性 - 臨時表undo(TEMP UNDO)Oracle
- 使用oracle 11g bbed dump undo header block報錯之系列六OracleHeaderBloC
- Oracle 面試寶典-UNDO篇Oracle面試
- 【REDO】Oracle redo undo 學習Oracle Redo
- Oracle常見UNDO等待事件Oracle事件
- Oracle undo 表空間管理Oracle
- oracle 釋放undo空間Oracle
- Oracle UNDO引數詳解Oracle
- [Oracle Script] Undo Usage Per statusOracle
- [Oracle Script] Undo Usage Per sessionOracleSession
- oracle重建UNDO表空間Oracle
- 淺談Oracle的undo管理Oracle
- oracle 查詢誰在用undoOracle