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

来源：这里教程网时间：2026-03-03 16:44:29 作者：

[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。