ANBOB™

ITL(Interested Transaction List ),The Oracle database engine locks the data modified by a transaction at the row level. To implement this feature every data block contains a list of all transactions that are modifying it. This list is commonly called interested transaction list (ITL). Its purpose is twofold. First, it is used to store information to identify a transaction as well as a reference to access the undo data associated to it. Second, it is referenced by every modified or locked row to indicate which transaction it is involved.

INITRANS

The initial number of slots composing the ITL is set through the INITRANS parameter. Even though it can be set to 1, which is the default value as well, as of 9i at least 2 slots are always created,and 8I it is 1 defaut. Note that the data dictionary lies to us on this matter. In fact, as shown in the following example, the data dictionary shows the value specified when the object was created and not the actual number of slots.

SQL> CREATE TABLE t (n NUMBER) INITRANS 1;

SQL> SELECT ini_trans FROM user_tables WHERE table_name = 'T';

 INI_TRANS
----------
   1

MAXTRANS

There is a maximum number of slots an ITL can contain. The actual maximum number depends on the blocks size. For example, an 8KB block can have up to 169 slots. Up to 9i the maximum is limited by the MAXTRANS parameter as well. As of 10g, however, this parameter is deprecated and, therefore, no longer honored. In the same way as for INITRANS, the data dictionary shows the value specified when the object was created and not the actual maximum number of slots.
Also note that while creating an object the database engine checks whether the MAXTRANS value is not greater than 255. And, if it is greater, it raises an ORA-02209 (invalid MAXTRANS option value).

How many slots are typically available in ITL ?

During the table creation, the INITRANS parameter defines how many slots are initially created in the ITL. INITRANS is a block level storage parameter which can be specified while creating an object (table). INITRANS and MAXTRANS parameters are used to control the concurrent access to the same block . When the transactions exhaust all the available slots and a new transaction comes in to lock a row, the ITL grows to create another slot. The ITL can grow up to the number defined by the MAXTRANS parameter of the table, provided there is space in the block. Nevertheless, if there is no more room in the block, even if the MAXTRANS is high enough, the ITL cannot grow. the maximum value for MAXTRANS parameter is 255.

Each ITL entry in the block transaction variable header takes 24 bytes. Though a block can have a maximum of 255 different ITL entries, the ITL allocation is limited by block size. The database block size plays an important role when allocating the number of inital ITL entries for the block. The rule is “the total size allocated for initial ITLs SHOULD be LESS THAN 50% of the database block size” .
i.e, sizeof(INITIAL ITLs) < ( 50% of the DATABASE BLOCK SIZE )

BLOCK SIZE(B) NO OF ITL entries allocated in block header
============ =============================
2048 41
4096 83
8192 169

ITL Waits

When a session requires a slot but all the available ones are in use by other active transactions, the database engine tries to dynamically create a new slot. This is of course only possible when a) the maximum number of slots was not already allocated b) enough free space (one slot occupies 24 bytes) is available in the block itself. If a new slot cannot be created, the session requiring it hits a so-called ITL wait. Note that the name of the actual wait event is called “enq: TX – allocate ITL entry”.
It is essential to point out that a session does not wait on the first slot becoming free. Instead, it probes, round-robin, the available slots to find out one that becomes free. And, while doing so, it waits few seconds on every one it probes. When during this short wait the slot becomes free, it uses it. Otherwise, it tries with another slot.
The actual implementation for finding a free slot is what Oracle changed in recent releases. So, let’s describe what the behavior in recent releases is.

ITL Waits in 11gR1

In 11.1.0.6 and 11.1.0.7 a session waits at most one time on every slot. For all slots but one it waits up to 5 seconds. For the other one it might wait indefinitely. The following pseudo code illustrates this (you should consider the variable called “itl” as an array referencing/containing all ITL slots).

FOR i IN itl.FIRST..itl.LAST
LOOP
  EXIT WHEN itl(i) IS FREE
  IF i <> itl.LAST
  THEN WAIT ON itl(i) FOR 5 SECONDS
  ELSE WAIT ON itl(i) FOREVER
  END IF
END LOOP

The problem of this algorithm is that an “unlucky” session might wait much longer than necessary. In fact, once it enters the WAIT FOREVER status, it no longer considers the other slots.

ITL Waits in 11gR2

In 11.2.0.1 and 11.2.0.2 a session might wait several times for the same slot. Initially the wait is short. As the time passes, the wait time increases exponentially based on the formula “wait time = power(2,iteration-1)”. For all slots but one there is a maximum wait time of 5 seconds, though. For the other one, and for the first 10 iterations only, the wait time is computed with the very same formula. Then, during the 11th iteration, the session waits indefinitely. The following pseudo code illustrates this.

iteration = 0
LOOP
  iteration++
  FOR i IN itl.FIRST..itl.LAST
  LOOP
    EXIT WHEN itl(i) IS FREE
    IF i <> itl.LAST
    THEN WAIT ON itl(i) FOR min(power(2,iteration-1),5) SECONDS
    ELSIF iteration <= 10
    THEN WAIT ON itl(i) FOR power(2,iteration-1) SECONDS
    ELSE WAIT ON itl(i) FOREVER
    END IF
  END LOOP
  EXIT WHEN free_itl_found
END LOOP

The advantage of this algorithm is that a session might probe several time all the available slots and, as a result, enters the WAIT FOREVER status after about 20 minutes only.

ITL Waits in 9i/10g

Up to 10.2.0.4 the behavior is similar to 11gR1. The only noticeable difference is that the wait time is not always 5 seconds. Instead, it is either 3 or 6 seconds. I was not able to spot a rule behind the choice between the two durations. So, there might be some randomness involved.
In 10.2.0.5 the behavior is similar to 11gR2. Also in this case the only noticeable difference is that the maximum wait time is not always 5 seconds. Instead, as in releases up to 10.2.0.4, it is either 3 or 6 seconds.

Extracted Christian Antognini’s article

打赏

微信扫一扫，打赏作者吧～