How to Troubleshoot Oracle Wait Event enq: TX - allocate ITL entry

Problem Description

How to Troubleshoot Oracle Wait Event enq: TX - allocate ITL entry

Explanation

ITL (Interested Transaction List) is related to every Data & Index Block. Before a Transaction begin it need to acquire the ITL on the block where the row need to be modified.

There is a small section in the block header of every DATA & INDEX block which has this ITL information (defined by the INITRANS & MAXTRANS parameter).

The ITL structure is created in the block during segment creation as per the INITRANS value and grows dynamically as concurrency occurs to a maximum of MAXTRANS value.

For every single transaction on that block there will be one ITL entry.

For example, if a INITRANS is 5 then there will be 5 ITL rows/entries in each block of that segment.

A session  waits for this event when it is unable to acquire an ITL entry in a block. The primary cause of ITL waits is that free slots in the ITL structure in the block are not available and it is unable to expand the ITL slots.

The lack of slots can be due to

a.       Low setting of the INITRANS & MAXTRANS, which reserves minimal ITL slots initially and place a hard limit on the number of transactions that can have locks on a block respectively

b.      And/Or, the block is so packed that there is no room for the ITL to grow

Troubleshooting Approach/Solution

Step 1

Find the object and the block related to this Wait Event

select a.sid,a.serial#,a.event,a.sql_id,a.ROW_WAIT_OBJ#, a.ROW_WAIT_FILE#,a.ROW_WAIT_BLOCK#,b.owner,b.object_type,b.object_name

from v$session a, dba_objects b

where a.event='enq: TX - allocate ITL entry' and a.ROW_WAIT_OBJ#=b.object_id;

Step 2

Find the INITRANS & MAXTRANS of the object

Table

select owner,table_name,INI_TRANS,MAX_TRANS from dba_tables where owner='SCOTT' and table_name='EMP';

Index

select owner,index_name,INI_TRANS,MAX_TRANS from dba_indexes where owner='SCOTT' and index_name='PK_EMP_IDX';

Step 3

Find the actual ITL slots available in the block header by dumping the block. You need to check the maximum available ITL slots and how many of them are free

              Command to Dump the Block

Alter system dump datafile block ;

              Find the Trace File Name

SELECT value FROM   v$diag_info WHERE  name = 'Default Trace File';

Identify the trace file and check for the ITL section (Note : A sample view of the ITL is provided below)

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

0x02   0x000a.01b.00000232  0x00c158cc.005c.2a  --U-    4  fsc 0x0000.000e593b

Step 4

You may want to suggest the following solution (depending on the scenario) to resolve this issue

a.       Increase INITRANS

b.      Increase PCTFREE

c.       Increase both INITRANS & PCTFREE

Note :

a.       All the above solution need a rebuild to take the impact

b.      Remember ASMM which may be part of your database.

c.       Increasing either INITRANS & PCTFREE is going to impact the block capacity in storing the rows