Check te Enq: TX – allocate ITL entry wait event in foregroud event in AWR

In AWR Report, we are getting the following wait event for one of the customer:

Top 5 Timed Foreground Events

Event Class                    Waits  Time(s) Avg wait (ms) % DB time   Wait Class
enq: TX - allocate ITL entry   1,800  5,143      3197       88.21       Configuration

Enq: Enqueue are Oracle locks which serialize the operations to the shared structure.
It is the lock on table or index. TX – means transaction.
ITL means ‘Interested Transaction List’ is the slot used by data block to access or modified the data in the block.When any session want to modified data block then he need ITL slot to access the data block.
ITL slot used as lock mechanism for a data block. We need ITL slot entry to modified the data in the block.

Cause: If data block has 1 ITL slot then one session is updating data block other session want to access the same data block then session need ITL slot for access the data of data block which cause these waiting events.

Solutions:

1. From AWR report get the table or index name causing event:
   Go to Segment Section in AWR –> Find the Segment by ITL Waits –> You will get the list of tables or indexes causing this wait event.
2. You need to increase the value of INITRANS for the table and indexes.(In some case MAXTRANS also).
3. If not fixed, then then we can also increase the PCTFREE of table or index.

Increase the value of INITRANS for the table and indexes:

-- Check current ini_trans value
col table_name for a40
select table_name,ini_trans,max_trans from dba_tables;

col index_name for a40
select index_name,ini_trans,max_trans from dba_indexes;

--Change the value of ini_trans 
--For table:
alter table table_name INITRANS 5;
alter table table_name move;
--For Index
alter index index_name rebuild INITRANS 5;

-- Example of changes:

SQL> select table_name,ini_trans from dba_tables where table_name = 'CONSUMER';
TABLE_NAME                                INI_TRANS
---------------------------------------- ----------
CONSUMER                                          1

SQL> alter table consumer initrans 10;
Table altered.

SQL> select table_name,ini_trans from dba_tables where table_name = 'CONSUMER';
TABLE_NAME                                INI_TRANS
---------------------------------------- ----------
CONSUMER                                         10

-- If you find, still having problem then use it:
SQL> alter table consumer move;
Table altered.

-- For index:
SQL> alter index CONSUMER_IDX rebuild initrans 10;
Index altered.

Increase the PCTFREE for the table or index:

-- If the AWR having same problem then we can also increase the PCT_FREE
Increasing PCTFREE for the table, leaving less rows per data block. Less data means less transaction lock on data block

--For table:
alter table table_name PCTFREE 20;
alter table table_name move;
--For index:
alter index index_name rebuild PCTFREE 20;