The segments ( table and it’s indexes)  are encountering High ITL (Interested Transaction List) waits, it is usually because of frequent inserts against blocks with insufficient space to support the demands of the concurrency requested of them.  So when a transaction occurs it marks the blocks that the transaction is ‘interested in’ modifying by placing an entry in the block’s ITL. As shown below by default the INITRANS value for a table is one  and for an index is two.

If there are insufficient free ITL slots then new ones will be taken in the free space reserved in the block. If this runs out and too many concurrent DML transactions are competing for the same data block then we observe contention against the following wait event – “enq: TX – allocate ITL entry”.

SQL> SELECT STATISTIC_NAME, VALUE FROM V$SEGMENT_STATISTICS WHERE OBJECT_NAME = '<Table or Index>'and VALUE > 200;

STATISTIC_NAME                   VALUE
--------------------------- ----------
logical reads                 25024144
db block changes               5300608
physical reads                  815272
physical writes                 260050
physical read requests          815272
physical write requests         234212
gc cr blocks received              281
gc current blocks received      698263
ITL waits                          575
row lock waits                   11068
space used                   108721232
space allocated              268435456

The database is affecting the “COMMIT/ROLLBACK”,is enq: TX – allocate ITL entry, – AWR report:

 

Enqueue Type (Request Reason)	Requests	Succ Gets	Failed Gets	Waits
TX-Transaction (allocate ITL entry)	4	4	0	2

Segments by ITL Waits (from AWR)

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	ITL Waits	% of Capture
TEST	TEST_DATA	TEST_INDEX1		INDEX	3	100.00

There is a small section of predefined ITL (defined by the MAXTRANS parameter) in the block header, the ITL structure grows dynamically as concurrency occurs. If there is no space to handle the concurrency then waits occur. The primary cause of ITL waits is that free slots in the ITL structure in the block are not available. The lack of slots can be due to 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 and/or, the block is so packed that there is no room for the ITL to grow Setting a high value of INITRANS will try to ensure that there are enough free slots in the ITL to hold all the concurrent transactions with a goal of minimal or no dynamic extension of the ITL. However, doing so also means that there is less space in the block for actual data, potentially increasing wasted space.

Another option is to try to make 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 may experience fewer buffer busy wait events, and an increase in performance.

SOLUTION
The main solution to this issue is to increase the ITL capability of the table or index by re-creating it and altering the INITRANS or PCTFREE parameter to be able to handle more concurrent transactions. This in turn will help to reduce “enq: TX – allocate ITL entry” wait events.

To reduce enq: TX – allocate ITL entry” wait events, We need to follow the steps below:

A Combination of increasing both INITRANS and PCTFREE

1) Set INITRANS to 50 and  pct_free to 40

alter table <table_name> PCTFREE 20  INITRANS 50;

2) Re-organize the table using move (alter table <table_name> move;)

3) Then rebuild all the indexes of the table as below

alter index <index_name>  rebuild PCTFREE 20 INITRANS 50;

SELECT TABLE_NAME,INI_TRANS FROM DBA_TABLES WHERE TABLE_NAME IN ('<table_name>');
SELECT PCT_FREE FROM DBA_TABLES WHERE TABLE_NAME IN ('<table_name>');

Note :  Move the Table
Syntax: ALTER TABLE MOVE

Advantages:
1. This operation is fast especially when compared to shrink.
2. This can be performed using simple commands.

Drawbacks:
1. Table is not available for DML during the move operation.
2. Additional space is needed.
3. Indexes need to be rebuilt manually post move operation.

Additional Note: Alter table move ONLINE can be used only for :
-> index-organized tables and for nested table storage tables that are index organized. Using ONLINE clause, DML operations on the IOTs are allowed during rebuilding of the primary key index of the table.
-> For moving partitions and sub partitions online.