我曾计划以评论的形式提供Index Quiz 2（索引测验2）的解答。但是，涉及太多的block dump,而且用发贴的方式会更容易一些。
问题1：我创建了一个表和索引，索引的initrans为4,然后向表中插入一行。当你dump索引时，在第一个索引块中会有多少个ITL（interested transaction list 事务槽–参见英文术语表）？
答1：按你对initrans的设置，表块中会显示有4个ITL，但是，索引块只会显示有2个条目–除非你在使用Oracle 8i及之前的版本（基本上，索引倾向于忽略initrans的设置，除非你重建索引或者是在已有数据上创建索引。）：
Object id on Block? Y
seg/obj: 0x183e3 csc: 0x00.5528ef6 itc: 2 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x000b.014.00004c37 0x008010b7.0259.1f --U- 1 fsc 0x0000.05528efe
如果你DUMP了索引块，你会注意到它被描述为““Leaf Block Dump”。当索引只有一个块时，则这个块是叶子块，尽管这个块也是做为根块的。随着索引的增长，根块最终会被重格式化为”Branch Block(分支块)"
当你第一次创建表和索引时，他们之间另一个小不同是根块是在你创建索引时被格式化。如果你在向表中插入数据前，DUMP出表块，你会发现只有段头块和空间管理位图块（如果使用的是ASSM）被格式化了，而数据块中仍然遗留有它们之前使用的痕迹。
问题2：在只使用一个会话，向表中插入300行后，根块分裂并且索引增长到3个数据块。那么根块和叶子块上的ITL会是什么情况？
答2：根块现在会被格式化为分支块，并且，它的ITL将只有1个条目（itc=1).这是分支块的特性–最终用户无法直接修改。唯一能改变它们的，是它们拆分时的“服务事务”，或者在它们关联的叶块拆分时，需要获取新条目时的“服务事务”-- 因此它们只需要一个ITL槽，称之为“service ITL”.
由于分支块上只有一个“service ITL”，如果在同一时刻，有两个叶块分裂，那么其中一个必须等待另一个完成后，释放“service ITL”。尽管在记录段统计数据的v$segstat中隐藏了它（第13号statistic：“service ITL waits”)，但在底层的X$表–x$ksolsstat中记录了这个等待事件。我相信这个等待事件是名为“enq: TX – index contention”的等待事件中所记录的其中之一。
Block header dump: 0x01400d0a
Object id on Block? Y
seg/obj: 0x183e3 csc: 0x00.5529151 itc: 1 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x001c.00e.000005b1 0x00802b9d.007c.01 -BU- 1 fsc 0x0000.05529156
叶子块上会持有两个ITL条目–一个为“service ITL” ，一个为最终用户的事务所使用。
Block header dump: 0x01400d0b
Object id on Block? Y
seg/obj: 0x183e3 csc: 0x00.5529153 itc: 2 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x001c.00e.000005b1 0x00802b9e.007c.01 -B-- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Block header dump: 0x01400d0c
Object id on Block? Y
seg/obj: 0x183e3 csc: 0x00.55291ae itc: 2 flg: O typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x001c.00e.000005b1 0x00802b9e.007c.02 CB-- 0 scn 0x0000.05529156
0x02 0x000c.003.0000314c 0x00801a48.012f.36 --U- 1 fsc 0x0000.055291b0
注意“service ITL” 的flag值中的第二位包含一个B值。根据Steve Adams在几年前发表的贴子的说法，这是要告诉我们UBA（undo block address undo块地址）中所引用的UNDO记录中包含有该ITL条目的undo信息。我只在分裂后的索引块中见过这个值，所以，我不确认这个解释是完全正确的（至少，对于较新的Oracle版本）。
问题3：重建表和索引，然后使用10个会话并发各自插入1行。在10行被插入完成前，不要提交。然后使用另一个会话插入300行以上。这三个索引块上的ITL会是什么情况？
答3：根块会再经历一次被格式化为分支块的过程，所以，它会有一个ITL条目（service ITL)。下面第一个DUMP内容是在插入10行后的根块。第二个DUMP内容是块分裂后的：
Block header dump: 0x01400f0a
Object id on Block? Y
seg/obj: 0x183e7 csc: 0x00.55292b2 itc: 11 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0010.023.00000d36 0x00803f2f.0093.0d --U- 1 fsc 0x0000.05529405
0x03 0x0013.015.00000d1f 0x00802436.00a6.0b --U- 1 fsc 0x0000.05529409
0x04 0x000e.02c.00000e29 0x008004bd.00c2.03 --U- 1 fsc 0x0000.05529415
0x05 0x0002.02d.0001582c 0x00801d33.2556.07 --U- 1 fsc 0x0000.05529417
0x06 0x0005.001.00013ec3 0x00800eb1.248b.38 --U- 1 fsc 0x0000.05529419
0x07 0x000f.004.00000e2c 0x00802739.009d.2b --U- 1 fsc 0x0000.0552941d
0x08 0x0019.01f.000005b3 0x008021ae.0055.0f --U- 1 fsc 0x0000.0552941f
0x09 0x0015.005.00000bf3 0x00800d42.007d.15 --U- 1 fsc 0x0000.05529421
0x0a 0x0008.008.00013ab9 0x00803c47.1d00.35 --U- 1 fsc 0x0000.05529424
0x0b 0x000b.00c.00004c37 0x008010b7.0259.3e --U- 1 fsc 0x0000.05529412
Block header dump: 0x01400f0a
Object id on Block? Y
seg/obj: 0x183e7 csc: 0x00.552960f itc: 1 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.019.00011880 0x00800db5.1cfc.01 -BU- 1 fsc 0x0000.05529616
注意，在块分裂前，我们有11个ITL条目。索引块上的第一个ITL条目，总是为“service ITL"保留的，以便块分裂可以在任何时候发生。由于我们有10个并发事务改变这个叶子块，所以，我们需要10多个ITL条目–总共11个–每一个未提交的事务一个。
分裂后，根块被重新格式化为分支块–注意块地址（0x01400f0a）没有改变。索引根块总是紧邻段头块之后，即使块分裂也不会改变。由于根块成为了分支块，它的ITL数目又回到了1个。
不过，我们来看一下在叶块上发生了什么：
Block header dump: 0x01400f0b
Object id on Block? Y
seg/obj: 0x183e7 csc: 0x00.5529613 itc: 11 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.019.00011880 0x00800db6.1cfc.01 -B-- 1 fsc 0x0000.00000000
0x02 0x0016.00f.00000934 0x008039f6.00a9.1c ---- 1 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x04 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x05 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x06 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x07 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x08 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x09 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0a 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0b 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Block header dump: 0x01400f0c
Object id on Block? Y
seg/obj: 0x183e7 csc: 0x00.552966c itc: 11 flg: O typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.019.00011880 0x00800db6.1cfc.02 CB-- 0 scn 0x0000.05529616
0x02 0x0007.02c.0000fcba 0x00800c7d.1dce.2f C--- 0 scn 0x0000.0552966c
0x03 0x0010.000.00000d37 0x00803f2f.0093.25 --U- 1 fsc 0x0000.0552966d
0x04 0x0012.013.00000e17 0x0080126a.0096.3d --U- 1 fsc 0x0000.0552966f
0x05 0x0018.02a.000005d0 0x008003f3.0056.16 --U- 1 fsc 0x0000.05529671
0x06 0x0013.024.00000d1f 0x00802436.00a6.1f --U- 1 fsc 0x0000.05529672
0x07 0x001a.019.000005c6 0x00800b48.005d.2b --U- 1 fsc 0x0000.05529674
0x08 0x001b.013.000005d6 0x00800ad8.00a0.0e --U- 1 fsc 0x0000.05529676
0x09 0x000a.01d.000106f1 0x00808d2b.1e45.2d --U- 1 fsc 0x0000.05529677
0x0a 0x001d.008.000005bc 0x00803ee4.0071.0f --U- 1 fsc 0x0000.05529679
0x0b 0x0017.004.000005c2 0x00800a83.0075.02 C--- 0 scn 0x0000.0552966a
我们又一次看到Oracle忽略了我们对initrans的设置。两个叶子块各自有11个ITL槽位，而不是我们期望的4个。每当一个叶子块分裂时，Oracle都会将现有的ITL复制到两个生成的叶子块中，一方面是因为这样更容易处理读取一致性，另一方面是因为一个活动事务很可能会锁定两个新块中的行。
想想这意味着什么–如果在索引的一个块中有一个短暂的并发活动突发，将ITL大小推高，那么从那个块中分裂出来的每个块（及其“后代”将有一个大的ITL——无论将来是否需要它……我们将在以后的文章中进一步研究这个想法。
原文链接及内容如下：
https://jonathanlewis.wordpress.com/2009/07/24/iq2-answers/
IQ2 – Answers
Filed under: Index Explosion,Indexing,Infrastructure,trace files,Troubleshooting — Jonathan Lewis @ 9:04 pm BST Jul 24,2009
I was planning to supply the answers to Index Quiz 2 as a comment – but there’s a lot of block dumps involved, and it’s easier to do that in postings.
Question 1: I’ve created a table and index with initrans 4, then inserted one row into the table. How many ITL (interested transaction list – see glossary) entries will there be in the first block of the index when you dump it.
Answer 1: The table block will show 4 entries in the ITL, obeying your setting for initrans, but the index block will show only two entries – unless you’re using Oracle 8i or earlier (Basically indexes tend to ignore the setting for initrans except when you rebuild an index, or create it on existing data.):
Block header dump: 0x01400d0a
Object id on Block? Y
seg/obj: 0x183e3 csc: 0x00.5528ef6 itc: 2 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x000b.014.00004c37 0x008010b7.0259.1f --U- 1 fsc 0x0000.05528efe
If you’ve done the index block dump, you’ll notice that the dump is described as a “Leaf Block Dump”. When an index consists of a single block, that block is a leaf block even though the block is also the root block. As the index grows the root block will eventually be reformatted as a “Branch Block”.
Another little difference between indexes and tables when you first create them is that the root block is formatted the moment you create the index. If you dumped the table block before inserting any data you would find that only the segment header block and (if you were using ASSM) the space management bitmap blocks none had been formatted, but the data blocks would still contain whatever garbage was left over from their previous usage.
Question 2: After inserting 300 rows from a single session the root block splits and the index grow to three blocks. What will the ITLs of the root and two leaf blocks look like.
Answer 2: The root block will now be formatted as a branch block, and its ITL will hold a single entry (itc = 1). That’s a feature of branch blocks – end user processes don’t modify them directly, the only thing that changes them is a “service transaction” as they split, or as they acquire new entries as one of their associated leaf blocks splits – so they only need one ITL slot, called the “service ITL”.
Since there is only one “service ITL” in a branch block, if two leaf blocks split at the same time then one of them has to wait for the other to complete and free up the “service ITL”. Although it’s hidden from v$segstat there is a segment statistic (number 13: “service ITL waits”) in x$ksolsstat – the underlying x$ table – that records this event. I believe that this event is also one of the events recorded by the wait called “enq: TX – index contention”.
Block header dump: 0x01400d0a
Object id on Block? Y
seg/obj: 0x183e3 csc: 0x00.5529151 itc: 1 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x001c.00e.000005b1 0x00802b9d.007c.01 -BU- 1 fsc 0x0000.05529156
The leaf blocks will hold two ITL entries – the “service ITL” and one ITL for end-user transactions.
Block header dump: 0x01400d0b
Object id on Block? Y
seg/obj: 0x183e3 csc: 0x00.5529153 itc: 2 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x001c.00e.000005b1 0x00802b9e.007c.01 -B-- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Block header dump: 0x01400d0c
Object id on Block? Y
seg/obj: 0x183e3 csc: 0x00.55291ae itc: 2 flg: O typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x001c.00e.000005b1 0x00802b9e.007c.02 CB-- 0 scn 0x0000.05529156
0x02 0x000c.003.0000314c 0x00801a48.012f.36 --U- 1 fsc 0x0000.055291b0
Note that the Flag value for the “service ITL” contains a B in the second place. According to a note published by Steve Adams a few years ago this is to tell us that the undo record reference in the Uba (undo block address) column contains the undo for the ITL entry. I’ve only ever seen this value set in index blocks after a block split – so I’m not sure that this explanation is entirely correct (at least, for more recent versions of Oracle).
Question 3: Recreate the table and index, then use 10 concurrent sessions to insert one row each. Don’t commit until all 10 rows are inserted. Then use another session to insert 300 more rows. What will the ITLs for the three index blocks look like.
Answer 3: Again the root block will be formatted as a branch block, so it will have a single (service) ITL entry. The first dump below is the root block just after inserting the first 10 rows, the second is after the block split:
Block header dump: 0x01400f0a
Object id on Block? Y
seg/obj: 0x183e7 csc: 0x00.55292b2 itc: 11 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0010.023.00000d36 0x00803f2f.0093.0d --U- 1 fsc 0x0000.05529405
0x03 0x0013.015.00000d1f 0x00802436.00a6.0b --U- 1 fsc 0x0000.05529409
0x04 0x000e.02c.00000e29 0x008004bd.00c2.03 --U- 1 fsc 0x0000.05529415
0x05 0x0002.02d.0001582c 0x00801d33.2556.07 --U- 1 fsc 0x0000.05529417
0x06 0x0005.001.00013ec3 0x00800eb1.248b.38 --U- 1 fsc 0x0000.05529419
0x07 0x000f.004.00000e2c 0x00802739.009d.2b --U- 1 fsc 0x0000.0552941d
0x08 0x0019.01f.000005b3 0x008021ae.0055.0f --U- 1 fsc 0x0000.0552941f
0x09 0x0015.005.00000bf3 0x00800d42.007d.15 --U- 1 fsc 0x0000.05529421
0x0a 0x0008.008.00013ab9 0x00803c47.1d00.35 --U- 1 fsc 0x0000.05529424
0x0b 0x000b.00c.00004c37 0x008010b7.0259.3e --U- 1 fsc 0x0000.05529412
Block header dump: 0x01400f0a
Object id on Block? Y
seg/obj: 0x183e7 csc: 0x00.552960f itc: 1 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.019.00011880 0x00800db5.1cfc.01 -BU- 1 fsc 0x0000.05529616
Note that we have 11 entries in the ITL before the split. The first ITL entry of any index block is always reserved for the “service ITL” so that a block split can take place whenever needed. Because we had 10 concurrent transactions changing this leaf block we needed 10 more ITL entries – for a total of 11 – one for each uncommitted transaction.
After the split the root block is reformatted as a branch block – note that the block address (0x01400f0a) has not changed. The root block of an index is always the block after the segment header block, and even a block split doesn’t change that. Since the root block has become a branch block, its ITL count drops back to one.
Look what has happened in the leaf blocks, though:
Block header dump: 0x01400f0b
Object id on Block? Y
seg/obj: 0x183e7 csc: 0x00.5529613 itc: 11 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.019.00011880 0x00800db6.1cfc.01 -B-- 1 fsc 0x0000.00000000
0x02 0x0016.00f.00000934 0x008039f6.00a9.1c ---- 1 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x04 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x05 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x06 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x07 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x08 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x09 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0a 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0b 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Block header dump: 0x01400f0c
Object id on Block? Y
seg/obj: 0x183e7 csc: 0x00.552966c itc: 11 flg: O typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.019.00011880 0x00800db6.1cfc.02 CB-- 0 scn 0x0000.05529616
0x02 0x0007.02c.0000fcba 0x00800c7d.1dce.2f C--- 0 scn 0x0000.0552966c
0x03 0x0010.000.00000d37 0x00803f2f.0093.25 --U- 1 fsc 0x0000.0552966d
0x04 0x0012.013.00000e17 0x0080126a.0096.3d --U- 1 fsc 0x0000.0552966f
0x05 0x0018.02a.000005d0 0x008003f3.0056.16 --U- 1 fsc 0x0000.05529671
0x06 0x0013.024.00000d1f 0x00802436.00a6.1f --U- 1 fsc 0x0000.05529672
0x07 0x001a.019.000005c6 0x00800b48.005d.2b --U- 1 fsc 0x0000.05529674
0x08 0x001b.013.000005d6 0x00800ad8.00a0.0e --U- 1 fsc 0x0000.05529676
0x09 0x000a.01d.000106f1 0x00808d2b.1e45.2d --U- 1 fsc 0x0000.05529677
0x0a 0x001d.008.000005bc 0x00803ee4.0071.0f --U- 1 fsc 0x0000.05529679
0x0b 0x0017.004.000005c2 0x00800a83.0075.02 C--- 0 scn 0x0000.0552966a
Again we see that Oracle has ignored our setting of initrans. Both leaf blocks have 11 slots in their ITLs, not the four that we might expect. Whenever a leaf block splits Oracle copies the existing ITL into both the resulting leaf blocks – partly because that makes it easier to deal with read-consistency, and partly because it’s quite possible that a single active transaction could be locking rows in both of the new blocks.
Think about what this means – if you have one brief burst of concurrent activity that pushes the ITL size up in just one block of the index then every block that splits off from that one block (and its “descendents” will have a large ITL – whether it’s going to be needed in future or not… and we’ll examine that thought a little more in a future article.
