Then follows the Interested Transaction List (Itl) which is used by Oracle to store critical information of any transaction making changes within the block. An index leaf block is basically assigned 2 ITL entries by default (but only 1 if it’s an index Branch block). However the number of pre-assigned ITL entries is set by the INITRANS physical attribute parameter of the index.

Any transaction wishing to make a change to the block must first be allocated to an ITL entry, with the first ITL entry (01) reserved by Oracle for internal recursive operations. If there’s an ITL entry that’s not being used by a current transaction, great, a new transaction will simply use a free ITL entry. However, if all ITL slots are already assigned to current transactions within the index block, then an additional ITL entry is created and allocated to the new transaction by Oracle, providing there’s sufficient free space within the block or the 255 limit hasn’t been reached (or the MAXTRANS parameter is not reached in older versions of Oracle).

As a simple demo to see this in action, I’m going to insert two additional rows into the table (and so create two new index entries in the index):

We see the transaction has used ITL slot number 02 as relevant details are now assigned.

Points of interest includes the Flag is now set to U (Unclean) and that the Lock attribute is assigned the value 2. This means the transaction has been committed but the index block has not been cleaned out and is currently “unclean” in that the 2 new index rows associated with the transaction still have the lock byte set (as we’ll see in a later post).

The other important piece of information is the value of the SCN within the ITL (0x0000.02d120c8). The SCN is basically a timestamp that denotes when the transaction made the associated changes to the index block. As this is the last transaction to have changed the block, this SCN effectively denotes the point of time when the block was last changed.

If we now also look at the start of the block dump and the block header details discussed in Part I of this series:

We notice the header has also been changed and now has the SCN from this transaction stamped in the header. Therefore any process needing this index block to be at a specific point in time (say a Select statement during a consistent read operation) can now quickly check the block header to see “when” it was last changed.

If the block has indeed changed since the (say) Select statement started, it need now only check the ITL slots to see which transaction has matching SCN details and so find the specific transaction that last changed the index block. Knowing the transaction in question, Oracle can then reference the Undo Block Address in the ITL entry and so determine the specific undo segment that contains details of the previous state of the block and rollback all changes made by this transaction and create a new consistent image of the block as it was prior to the transaction making the block changes. The Select statement can now check the previous SCN in the header and repeat the same process as necessary until the index block is at a point in time prior to the Select statement starting.

In short, basically how consistent reads are implemented in Oracle.

Everything I’ve discussed so far in Parts I & II are just as applicable to tables (and other segments) as they are to indexes. In the next post, we’ll look at the next portion of an index block dump which is unique to index segments …