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 blocks before inserting any data you would find that only the segment header block and (if you were using ASSM) the space management bitmap blocks 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.
Oracle Scratchpad 
[…] IQ2 – Answers […]
Pingback by Index Quiz 2 « Oracle Scratchpad — July 24, 2009 @ 9:06 pm BST Jul 24,2009 |
“Both leaf blocks have 11 slots in their ITLs, not the eight that we might expect”
Shouldn’t it be 4?
Just so I can get this straight: if a single session inserts 300 rows (or something enough to cause another split) after the other 10 concurrent sessions have commited, then all the “child” blocks will inherit 11 ITL entries, including the service ITL?
Another question: how do you know if a block is the root, branch or leaf block?
Comment by Daniel Stolf — July 24, 2009 @ 10:13 pm BST Jul 24,2009 |
Daniel,
You’re right, it should be four; thanks for pointing out the error. Now corrected.
Yes, every leaf that splits off from either of those two will inherit 11 ITL entries.
If you do a block dump, the phrase “Leaf block dump” or “Branch block dump” will appear in the header section. However, you cannot, in general, identify a block as “the root block” of an index by looking at the dump – you have to know that it is the first block after the segment header.
If you happen to know the blevel or height (= blevel + 1) of the index, then you can search for the entry kdxcolev in the dump as this records the level of the block in the index – and if kdxcolev = blevel then you are looking at the root block.
Comment by Jonathan Lewis — July 24, 2009 @ 10:37 pm BST Jul 24,2009 |
Thank you for explaining the 11th ITL entry it was making me crazy :)
Comment by coskan — July 24, 2009 @ 11:11 pm BST Jul 24,2009 |
Nice.
When “playing” the “Index Quiz 2” I was thinking on this (ehm…wrong) model:
– every index block start with 4 itc (because I say I want 4 ITC)
– on block split every block is “initialized” with 4 itc
– the itc can grow to MAXTRANS
Quite linear, but quite expensive also because every time a new block is added you have to read the initial INITRANS value.
The implementation explained by Jonathan is very time effective: it minimize time creation of the block (you have to “copy” the data from the block you are splitting, so no additional look up is required), it also try to optimize concurrency (block are created with a “high” itc so the index is ready for future high transaction burst)
Comment by lascoltodelvenerdi — July 27, 2009 @ 7:04 am BST Jul 27,2009 |
lascoltodelvenerdi,
Just playing devil’s advocate and talking around the possibilities:
a) looking up initrans need not be expensive – especially compared to clearing a buffer for the split and writing all that undo and redo; if it gets expensive you could alway ensure that initrans is copied into the root block, and you will probably have pinned the root block already.
b) the drawback to cloning the ITL is that a single burst of concurrency once in the life time of the index could leave a lot of excess space in the ITL for all new blocks from there onwards. I can’t see any obvious reason why the copy process doesn’t avoid copying ITL entries which have not been used when a split occurs, as this might help to minimise the future wastage.
Comment by Jonathan Lewis — July 27, 2009 @ 8:15 pm BST Jul 27,2009 |
on b.
You are right (as always! lol!) the reasons can be: HD-space cost is very low (so, some wastage is “not a great problem”) or when split occurs you already got the itc so just copy it (lazy programming technique).
Possible future enhancements? Maybe…
As a conclusion:
When you need to rebuild your index?
When you got a lot of excess space in the ITL! ;-)
(just kidding a little bit)
Comment by lascoltodelvenerdi — July 28, 2009 @ 6:05 am BST Jul 28,2009 |
lascoltodelvenerdi,
I don’t really see this enhancement coming along in the near future – the strategy of copying the ITL has been around since version 6.
Funny you should mention index rebuilding though …
Comment by Jonathan Lewis — July 28, 2009 @ 7:47 pm BST Jul 28,2009 |
[…] Index Quiz 2 : Answering the question with block dumps […]
Pingback by Index ITLs « Oracle Scratchpad — September 19, 2009 @ 3:36 pm BST Sep 19,2009 |
[…] and fails on the 169th – there’s one ITL in the index ITL area that is reserved for the “service ITL” (see answer 2), the one that Oracle uses to register and hold the block on an “index node […]
Pingback by Index ITL Limit | Oracle Scratchpad — February 18, 2022 @ 5:03 pm GMT Feb 18,2022 |