How to Troubleshoot Oracle Wait Event enq: TX - allocate ITL entry
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
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;
Find the INITRANS & MAXTRANS of the object
select owner,table_name,INI_TRANS,MAX_TRANS from dba_tables where owner='SCOTT' and table_name='EMP';
select owner,index_name,INI_TRANS,MAX_TRANS from dba_indexes where owner='SCOTT' and index_name='PK_EMP_IDX';
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
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
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
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