Category - TUNING

Wait Event Demystified - enq: TX - allocate ITL entry -Interested Transaction Lists (ITL) Waits

Interested Transaction Lists (ITL) Waits

In this doc we will be working on ITL wait event "enq: TX - allocate ITL entry " and it's resolutions.

What is a TX lock?

A TX lock is acquired when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. It is used mainly as a queuing mechanism so that other sessions can wait for the transaction to complete. The lock name (ID1 and ID2) of the TX lock reflect the transaction ID of the active transaction.

Waits due to Insufficient 'ITL' slots in a Block

Oracle keeps note of which rows are locked by which transaction in an area at the top of each data block known as the 'interested transaction list'. The number of ITL slots in any block in an object is controlled by the INITRANS and MAXTRANS attributes. INITRANS is the number of slots initially created in a block when it is first used, while MAXTRANS places an upper bound on the number of entries allowed. Each transaction which wants to modify a block requires a slot in this 'ITL' list in the block.

MAXTRANS places an upper bound on the number of concurrent transactions which can be active at any single point in time within a block.

INITRANS provides a minimum guaranteed 'per-block' concurrency.

If there is no free 'ITL' then the requesting session will wait on one of the active transaction locks in mode 4.

In earlier releases of Oracle Database, the MAXTRANS parameter limited the number of transaction entries that could concurrently use data in a data block. This parameter has been deprecated in 10g and higher. Oracle Database now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.

ITL contention is likely to generate waits for the 'enq: TX - allocate ITL entry'  wait event.  
If need be, increase INITTRANS and MAXTRANS to resolve this.

Interested Transaction Lists (ITL)

When a row is locked by a transaction, that information is placed in the block header where the row is located. When another transaction wishes to acquire the lock on the same row, it has to travel to the block containing the row anyway, and upon reaching there, it can easily identify that the row is locked, from the block header. There is no need to queue up for some single resource like a lock manager. This makes applications immensely scalable.

So what portion of the block header contains information on locking? It is a simple data structure called Interested Transaction List (ITL), a linked list data structure that maintains information on transaction address and rowid. ITL contains several slots for transactions. When a row in the block is locked for the first time, the transaction places a lock in one of the slots with the rowid of the row that is has locked. In other words, the Transactions is Interested in the row, hence the name Interested Transaction List. When the same transaction or another one locks another row, the information is stored in another slot and so on and so forth.

So how many slots are available? During the table creation, the parameter INITRANS parameter defines how many slots are initially created in the ITL. 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. But to what extent can it grow? It can grow up to the number defined by the MAXTRANS parameter of the table, provided there is space in the block.
What is an ITL Wait

If a block is so packed that the the ITL cannot grow to create a free slot, then the transaction that needs to lock a row waits, till a slot becomes available. This wait is termed as ITL waits and can be seen when from v$session_wait is the session is waiting. Let's see these description in action. Assume our table has  INITRANS of 1 and MAXTRANS 11. A typical data block right after the creation of the table will look like this.

How to Reduce ITL Waits

The primary cause of ITL waits is the unavailability of free slots in the ITL. These can be due to

    low setting of the MAXTRANS, which places a hard limit on the number of transactions that can have locks on a block
    or, the block is so packed that there is no room for the ITL to grow OR
    or both

Therefore, setting a high value of INITRANS will make sure that there are enough free slots in the ITL and there will be minimal or no dynamic extension of the ITL. However this also means that there is less space in the block for actual data, increasing wasted space.

The other option is to making sure the data is less packed so that ITL can grow enough to accommodate the surges in ITL. This can be done by increasing PCTFREE, increasing FREELISTS and FREELIST GROUPS parameters for a table. This will make a block hold less data and more room for the ITL to grow. As a direct result of the reduction in packing, the table will experience less buffer busy waits event, increasing performance.


 

DEMONSTRATION OF 'enq: TX - allocate ITL entry'  wait event.  

In this Demo we will create Table with 3 rows and 2 columns. 
Columns vlues are so large that it will FILL COMPLETEY all three rows and Blocks as well and there will be no space left in BLock except ITL-entry( default-2).

Step 1=> Create Table with 3 Rows with completely packed datablocks as below.

SQL> CREATE TABLE ITL_TEST (n NUMBER, c VARCHAR2(4000)) PCTFREE 0;

SQL>INSERT INTO ITL_TEST SELECT rownum, rpad('*',2679,'*') FROM dual CONNECT BY level <= 3;

SQL>COMMIT;

Table created.

3 rows created.

Commit complete.

Step 2=> Now we will check INITRANS,MAXTRANS values for this table as below

SQL> select owner,table_name,pct_used,pct_free,ini_trans,max_trans from dba_Tables where table_name='ITL_TEST';

OWNER					 TABLE_NAME				    PCT_USED   PCT_FREE  INI_TRANS  MAX_TRANS
---------------------------------------- ---------------------------------------- ---------- ---------- ---------- ----------
ABHI_TEST				 ITL_TEST						      0 	 1	  255

SQL> 

Step 3=> Let we check ITL slots available using DUMPING of datafiles as below,

-> Get Datafile and BLock numbers as below.

SQL>  select rowid rid, dbms_rowid.rowid_relative_fno(rowid) relative_fno, dbms_rowid.rowid_block_number(rowid) block_id from ITL_TEST;

RID		   RELATIVE_FNO   BLOCK_ID
------------------ ------------ ----------
AAAR5bAAHAAAAFzAAA	      7        371
AAAR5bAAHAAAAFzAAB	      7        371
AAAR5bAAHAAAAFzAAC	      7        371

SQL> 

-> Dump datafile and BLock as below.

SQL>  alter session set tracefile_identifier ='Block_dump';

Session altered.

SQL> alter system dump datafile 7 block 371;

System altered.

-> Open trace file and look for ITL SLOTS as below.

As we can see ONLY 2 ITL Slots are availble in this BLOCK. So if we execute 3 transactions (which is greater than existing ITL Slots) there will be ITL-Wait events.

[oracle@racsetn2 trace]$ more NTNT2_ora_26102_Block_dump.trc

 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0002.010.0000039c  0x01003612.00d3.23  --U-    3  fsc 0x0000.00268a1e           ->>> ITL slot1
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000            ->>> ITL slot2
bdba: 0x01c00173
data_block_dump,data header at 0xf2bc0064

Step 4=>Now check data allocation in Blocks as below,

SQL> SELECT dbms_rowid.rowid_block_number(rowid), n FROM ITL_TEST ORDER BY n;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)	      N
------------------------------------ ----------
				 371	     10
				 371	     20
				 371	     30

SQL> 

as we can see as per above snapshot block allocation same as given.


Step 5=>Now we will start 3 different session as below.

Sess#1

[oracle@racsetn1 Desktop]$ sqlplus ABHI_TEST/Oracle_4U

SQL> sELECT sid FROM v$session WHERE audsid = userenv('sessionid');

       SID
----------
       279

SQL> UPDATE ITL_TEST SET n=n*10 WHERE n = 3;

1 row updated.

Sess#2

[oracle@racsetn1 Desktop]$ sqlplus ABHI_TEST/Oracle_4U

SQL> sELECT sid FROM v$session WHERE audsid = userenv('sessionid');

       SID
----------
       287

SQL> UPDATE ITL_TEST SET n=n*10 WHERE n = 1;

1 row updated.

Sess#3

[oracle@racsetn1 Desktop]$ sqlplus ABHI_TEST/Oracle_4U (===>>> this session will be in waiting).

SQL> sELECT sid FROM v$session WHERE audsid = userenv('sessionid');

       SID
----------
    24

SQL> UPDATE ITL_TEST SET n=n*10 WHERE n = 2;

WAITING !!!!!!!!!!!!

Step 6=> Login from DBA user and check for Wait event as below,

As we can see ,SID 24 is Waiting for "enq: TX - allocate ITL entry "

SQL>  select sid,event,username from gv$session where username ='ABHI_TEST';

       SID                    EVENT                     USERNAME
--------------------------------------------------------------------------------
    24 enq: TX - allocate ITL entry                ABHI_TEST

    279 SQL*Net message from client          ABHI_TEST

   287 SQL*Net message from client        ABHI_TEST

How to resolve -> To resolve this we nee to Commit transaction in Sess#1.

ASH report For ITL WAIT EVENT.

We can see ITL Wait in ASH reports as below.





Reference => Waits for 'Enq: TX - ...' Type Events - Transaction (TX) Lock Example Scenarios (Doc ID 62354.1)
          => Troubleshooting waits for 'enq: TX - allocate ITL entry' (Doc ID 1472175.1)
          => INITRANS AND MAXTRANS STORAGE PARAMETERS (Doc ID 1020573.102)
          => https://antognini.ch/2013/05/itl-deadlocks-script/
          => https://antognini.ch/2011/04/itl-waits-changes-in-recent-releases/#comment-486