[20210604]索引分裂與 itl ktbitflg.txt

lfree發表於2021-06-04

[20210604]索引分裂與 itl ktbitflg.txt

--//前幾天在library cache 轉儲的測試中,重新複習Oracle Core Essential Internals for DBAs and Developers的中文版,無意中發
--//現如下:

--//轉抄 英文版PDF文件內容 P41:
Table 3-2. Columns in the Interested Transaction List
-----------------------------------------------------------------------------------------------------
Column       Description
-----------------------------------------------------------------------------------------------------
...
Flag         Bit flag identifying the apparent state of this transaction:  
             ----: active (or "never existed" if every field in the Xid is zero).
             --U-: Upper bound commit (also set during "fast commit").
             C---: Committed and cleaned out (all associated lock bytes have been reset to zero).
             -B--: May be relevant to the recursive transactions for index block splits. I have seen
                   comments that this flag means the UBA will point to a record holding the previous
                   content of the ITL entry, but I have not managed to confirm this.
             ---T: I have seen comments that this means the transaction was active during block
                    cleanout, but I have not managed to confirm this.
-----------------------------------------------------------------------------------------------------

--//裡面提到-B--標識與索引分裂有關.裡面提到了recursive transactions,既然是遞規事務表示不會回滾的,應該檢視索引分裂時可以
--//看到這個標識.自己測試看看,另外後面的內容實在不好理解:

--//I have seen comments that this flag means the UBA will point to a record holding the previous content of the ITL
--//entry, but I have not managed to confirm this.

--//我仔細看了中文的翻譯,感覺不好理解.感覺應該翻譯成:
--//我曾看過評論這個標記意味UBA會指向持有先前內容的ITL項.但我無法確認.
--//總之既然相關索引分裂,應該可以看到這個-B--標識.

--//金山詞霸的翻譯:
--//可能與索引塊分割的遞迴事務相關。我看到評論,這個標誌意味著UBA將指向儲存ITL條目的之前內容的記錄,但我沒有確認這一點。

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試:
--//session 1:
drop table t1 purge;
create table t1 ( id number ,name varchar2(100)) ;
create index i_t1_id on t1(id);
alter session set events '10224 trace name context forever,level 1';
insert into t1 select rownum,lpad('a',100,rownum) from dual connect by level<=540;
commit;
--//注:插入540正好索引根節點滿了,繼續插入索引會發生分裂.

--//session 2:
SCOTT@book> alter system checkpoint ;
System altered.

SCOTT@book> alter system checkpoint ;
System altered.

SCOTT@book> @ treedump.sql  i_t1_id
 OBJECT_ID
----------
     92871
Session altered.

--//跟蹤內容如下:
*** 2021-06-04 08:54:57.989
leaf: 0x10002b3 16777907 (0: nrow: 540 rrow: 540)
----- end tree dump

0x10002b3 = set dba 4,691 = alter system dump datafile 4 block 691 = 16777907


SCOTT@book> alter system dump datafile 4 block 691;
System altered.

--//跟蹤內容如下:
Block header dump:  0x010002b3
 Object id on Block? Y
 seg/obj: 0x16ac7  csc: 0x03.17eafa32  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10002b0 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x000a.006.00009010  0x00c00425.1d8f.0a  --U-  540  fsc 0x0000.17eafa42

--//session 1:
SCOTT@book> insert into t1 values (400,lpad('b',100,'b'));
1 row created.

--//注意不要提交!!
--//session 2:

SCOTT@book> @ tix
New tracefile_identifier =  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_44988_0001.trc

SCOTT@book> @ treedump.sql i_t1_id
 OBJECT_ID
----------
     92871
Session altered.

--//跟蹤內容如下:
----- begin tree dump
branch: 0x10002b3 16777907 (0: nrow: 2, level: 1)
   leaf: 0x10002b5 16777909 (-1: nrow: 279 rrow: 279)
   leaf: 0x10002b6 16777910 (0: nrow: 262 rrow: 262)
----- end tree dump
--//可以確定索引已經發生了分裂.

3.轉儲塊分析:
SCOTT@book> alter system checkpoint ;
System altered.

SCOTT@book> alter system checkpoint ;
System altered.

--//0x10002b3 = set dba 4,691 = alter system dump datafile 4 block 691 = 16777907
--//0x10002b5 = set dba 4,693 = alter system dump datafile 4 block 693 = 16777909
--//0x10002b6 = set dba 4,694 = alter system dump datafile 4 block 694 = 16777910
--//索引鍵值400應該插入dba=0x10002b6.

SCOTT@book> alter system checkpoint ;
System altered.

SCOTT@book> alter system checkpoint ;
System altered.

SCOTT@book> @ tix
New tracefile_identifier =  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_44988_0002.trc

SCOTT@book> alter system dump datafile 4 block 691;
System altered.

--//跟蹤內容如下:
Block header dump:  0x010002b3
 Object id on Block? Y
 seg/obj: 0x16ac7  csc: 0x03.17eafb00  itc: 1  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10002b0 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.016.00001020  0x00c012d7.0776.01  -BU-    1  fsc 0x0000.17eafb02

--//確實看到了B標識,注意還有一個U標識,表示已經提交。
--//奇怪一點原來2個ITL槽變成1個ITL槽了。

SCOTT@book> @ tix
New tracefile_identifier =  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_44988_0003.trc

SCOTT@book> alter system dump datafile 4 block 694;
System altered.

--//跟蹤內容如下:
Block header dump:  0x010002b6
 Object id on Block? Y
 seg/obj: 0x16ac7  csc: 0x03.17eafb01  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10002b0 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.016.00001020  0x00c012d8.0777.02  -B--    1  fsc 0x0000.00000000
0x02   0x000a.000.0000900c  0x00c00424.1d8f.1c  ----    1  fsc 0x0000.00000000

--//注意看ITL第1個槽用於分裂事務的,注意看XID=0x0009.016.00001020,與塊0x010002b3一致。也就是同一個事務。
--//再看UBA=0x00c012d8.0777.02,而前面的塊0x010002b3,記錄的UBA=0x00c012d7.0776.01.
--//按照順序號0x776,根節點事務在前,葉子節點順序號0x777在後。使用的undo塊不一樣。

--//0x00c012d7 = set dba 3,4823 = alter system dump datafile 3 block 4823 = 12587735
--//0x00c012d8 = set dba 3,4824 = alter system dump datafile 3 block 4824 = 12587736

--//session 1:
SCOTT@book> @ xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.0.36876

--//36876 = 0x900c,可以發現ITL第2個槽記錄的事務XID一致。XID=0x000a.000.0000900c。

C70                                                                        XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS            USED_UBLK  USED_UREC XID              ADDR             START_DATE                FLAG
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ----------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 0 36876;           10          0      36876          3       1060       7567         28 ACTIVE                    1          2 0A0000000C900000 00000000857D0FA8 2021-06-04 08:57:15       3587
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 1060;

4.回滾測試看看:
SCOTT@book> rollback ;
Rollback complete.

SCOTT@book> @ tix
New tracefile_identifier =  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_44988_0004.trc

SCOTT@book> alter system checkpoint ;
System altered.

SCOTT@book> alter system dump datafile 4 block 691;
System altered.

--//跟蹤內容如下:
Block header dump:  0x010002b3
 Object id on Block? Y
 seg/obj: 0x16ac7  csc: 0x03.17eafb00  itc: 1  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10002b0 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.016.00001020  0x00c012d7.0776.01  -BU-    1  fsc 0x0000.17eafb02

SCOTT@book> alter system dump datafile 4 block 694;
System altered.

--//跟蹤內容如下:
Block header dump:  0x010002b6
 Object id on Block? Y
 seg/obj: 0x16ac7  csc: 0x03.17eafb01  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10002b0 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.016.00001020  0x00c012d8.0777.02  CBU-    0  scn 0x0003.17eb0156
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

--//事務取消,最後標識是提交C。第2個ITL槽全部變為0.塊分裂已經發生並提交,這個遞迴事務不會rollback。



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2775396/,如需轉載,請註明出處,否則將追究法律責任。

相關文章