AWR TOP 5 Timed Events Analysis: Top 5 Timed Foreground Events - enq: TX

enq: TX - allocate ITL entry (Interested Transaction Lists)

Enqueues are locks that coordinate access to database resources. This event indicates that the session is waiting for a lock that is held by another session.

The name of the enqueue is included as part of the wait event name, in the form enq: enqueue_type - related_details. In some cases, the same enqueue type can be held for different purposes, such as the following related TX types:
enq: TX - allocate ITL entry
enq: TX - contention
enq: TX - index contention
enq: TX - row lock contention

These are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.

The event “enq: TX - allocate ITL entry” indicates “enqueues” (locks) that are waiting for serial access to Oracle resources, in this case the transaction is waiting for a lock that is held by a competing session.

When too many concurrent DML transactions are competing for the same data block we observe this wait event - " enq: TX - allocate ITL entry"

One can find out the segments which are associated with this wait event in the same AWR under the “Segments by ITL Waits” section

By looking at the Segments by ITL waits we can find out the segments which are experiencing this wait event are Indexes.

One can also find out by running the below Sql’s

select
   sql_id,
   event,
   count(*),
   lpad(round(ratio_to_report(count(*)) over () * 100)||'%',10,' ') percent
from
   dba_hist_active_sess_history
where
   sample_time between timestamp 'XXXX-XX-XX 10:00:00'
and
   timestamp 'XXXX-XX-XX 11:00:00'
and
   event = 'enq: TX - allocate ITL entry'
group by
   sql_id, event
order by
   percent desc;

Once you know the SQL_ID (from the above query, you can plug it into this query to find the exact object that is experiencing the wait:

select *
from (
   select
      ash.sql_id,
      ash.current_obj#,
      obj.object_name,
      count(*) as wait_count
   from
      dba_hist_active_sess_history ash
   left join
      dba_objects obj
   on
      obj.object_id = ash.current_obj#
   where
      ash.sample_time between timestamp 'XXXX-XX-XX 10:00:00'
   and
      timestamp 'XXXX-XX-XX 11:00:00'
   group by
      ash.sql_id,
      ash.top_level_sql_id,
      ash.current_obj#,
      obj.object_name
   order by
      4 desc
)
where
   sql_id = 'xxxxxx'
and
   rownum <= 10;

By default INITRANS value for table is 1 and for index is 2. When too many concurrent DML transactions are competing for the same data block we observe this wait event - " enq: TX - allocate ITL entry"

Solutions

Once the table or index is reorganized by altering the INITRANS or PCTFREE parameter, it helps to reduce "enq: TX - allocate ITL entry" wait events.

To reduce enq: TX - allocate ITL entry" wait events, We need to follow the below steps.

Solution 1

1) Depending on the amount of transactions in the table we need to alter the value of INITRANS.

alter table <table name> INITRANS 50;

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

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

alter index <index_name> rebuild INITRANS 50;

If the issue is not resolved by the above steps, please try by increasing PCTFREE

Solution 2

1) Spreading rows into more number of blocks will also helps to reduce this wait event.

alter table <table name> PCTFREE 40;

2) Then re-organize the table using move (alter table service_T move;)

3) Rebuild index

alter index index_name rebuild PCTFREE 40;

OR You can combine steps A and B as below

1) Set INITRANS to 50 pct_free to 40

alter table <table_name> PCTFREE 40 INITRANS 50;

2) Then 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 40 INITRANS 50;

NOTE:

The table/index can be altered to set the new value for INITRANS. But the altered value takes effect for new blocks only. Basically you need to rebuild the objects so that the blocks are initialized again.

For an index this means the index needs to be rebuild or recreated.

For a table this can be achieved through: