enq: TX - allocate ITL entry

What is ITL (Interested Transaction List) ? ITL(Interested Transaction List) is a table which is stored in a block header to manage concurrent access to data within that block and maintain read consistency. When a transaction starts, Oracle assignes a unique number called as Transaction ID (XID). This transaction ID(XID) is a undo segment number, slot, and sequence number. This table also stores information like the transactions have placed locks on this block and which rows have committed and uncommitted data.  Let's understand "enq: TX - allocate ITL entry" : When a transaction requires to make any changes to a block, it requires an entry in the ITL to record its transaction information in that block. Let's assume that if multiple concurrent transactions want to update rows in the same block and if all ITL slots within that block are occupied or full, new transactions must wait which leads to contention in the database. This contention is called as "enq: TX - allocate ITL entry". Let's troubleshoot a real world scenario which was happened in one of the production database. Problem Statement: Application team reported slowness in one of the application Job "CBSAPP". This job typically completes on time as part of the daily schedule; however, on one occasion, it experienced significant slowness, resulting delay in daily operational processing. Troubleshooting: Based on the database session logs, it’s clear that the CBSAPP job (SQL_ID: 91pcf7gjtx7jq) was not progressing, because it was stuck waiting on the event "enq: TX - allocate ITL entry". set lines 300 pages 20000 col username for a15 col event for a30 col module for a35 col process for a10 col machine for a15 set time on timing on select INST_ID,SID, SERIAL#, USERNAME, STATUS, SQL_ID, MODULE,MACHINE, LAST_CALL_ET/60, BLOCKING_SESSION BLK_SESS, EVENT from gv$session where username is not null and status='ACTIVE' order by 10; sql_id : 91pcf7gjtx7jq Query Text: update RMAP set SY_DATE=:b1,STAT=:b2,EOD_FLAG=:b3,CON_NO=:b4 where (INST_NO=:b5 and ACCOUNT_NO=:b6); Let's check the size of the table and table properties like PCT_FREE and INI_TRANS values. SQL> select sum(bytes/1024/1024/1024) SIZE_GB from dba_segments where segment_name='RMAP'; SIZE_GB ------------ 3.22265625 set lines 200 pages 1000 col owner for a15 col table_name for a20 col table_owner for a15 col partition_name for a20 select owner, table_name, pct_free, ini_trans, last_analyzed, num_rows, partitioned from dba_tables where table_name='RMAP'; OWNER    TABLE_NAME PCT_FREE INI_TRANS LAST_ANA NUM_ROWS PAR TABLESPACE_NAME -------- ---------- -------- --------- -------- -------- --- --------------- APPSUSER  RMAP             5         5 20250815 68939705 NO  TBSIDX_100M set lines 200 pages 1000 col owner for a15 col index_name for a25 col table_name for a23 select owner,index_name,status,pct_free, ini_trans, last_analyzed, blevel, clustering_factor, partitioned from dba_indexes where table_name='RMAP'; OWNER    INDEX_NAME  STATUS PCT_FREE INI_TRANS LAST_ANA BLEVEL CLUSTERING_FACTOR PAR TABLESPACE_NAME -------- ----------- ------ -------- --------- -------- ------ ----------------- --- --------------- APPSUSER  RMAPPK      VALID        5         5 20250815      3          60292565 NO  TBSIDX_100M Plan of action to fix the issue: To fix "enq: TX - allocate ITL entry"  wait issue, you need to increase INITRANS or PCTFREE values of table or index to process more concurrent transactions. Let's increase the INI_TRANS and PCT_FREE values and check the performance. Note: Please note that when you change INITRANS value of a table or index, the new value only applies to newly allocated blocks, not to existing ones. To apply the new INITRANS setting to existing blocks, you need to reorganize or rebuild the object so that the blocks will initialize again. #Change the PCT_FREE and INI_TRANS values for table and index. SQL> alter table APPSUSER.RMAP PCTFREE 30 INI_TRANS 100; Table altered. SQL> alter table APPSUSER.RMAPPK PCTFREE 30 INI_TRANS 101; Table altered. #Re-org the table by moving into same tablespace. SQL> alter table APPSUSER.RMAP move; Table altered. #Rebuild the index. SQL> alter index APPSUSER.RMAPPK rebuild online parallel 10; Index altered. SQL> alter index APPSUSER.RMAPPK noparallel; Index altered. #Gather the stats on table. SQL> exec dbms_stats.GATHER_TABLE_STATS (OWNNAME=>'APPSUSER',TABNAME=>'RMAP',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size, METHOD_OPT=>'for all columns size 1',DEGREE=>32,CASCADE=>TRUE); PL/SQL procedure successfully completed. #Check table and index properties. set lines 200 pages 1000 col owner for a15 col table_name for a20 col table_owner for a15 col partition_name for a20 select owner, table_name, pct_free, ini_trans, last_analyzed, num_rows, partitioned from dba_tables where table_name='RMAP'; OWNER    TABLE_NAME PCT_FREE  INI_TRANS LAST_ANALYZED       NUM_ROWS PAR -------- ---------- -------- ---------- ------------------- -------- --- APPSUSER  RMAP            30        100 25-08-2025 19:15:37 69153690 NO set lines 200 pages 1000 col owner for a15 col index_name for a25 col table_name for a23 select owner,index_name,status,pct_free, ini_trans, last_analyzed, blevel, clustering_factor, partitioned from dba_indexes where table_name='RMAP'; OWNER     INDEX_NAME STATUS   PCT_FREE INI_TRANS LAST_ANA BLEVEL CLUSTERING_FACTOR PAR --------- ---------- -------- -------- --------- -------- ------ ----------------- --- APPSUSER  RMAPPK     VALID          30       101 20250825      3          60723242 NO After implementing the parameter changes, the same job was rerun, and the issue appeared to be resolved. The job completed in significantly less time, and the previously stuck update query executed within seconds. The following query was the next in sequence within the job and was executed immediately after the above update query:

Thanks for reading this post ! Please comment if you like this post ! Click FOLLOW to get future blog updates !