[20210604]索引分裂与 itl ktbitflg.txt
[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。
