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:
- exp/imp
- alter table move
- dbms_redefenition
If one has to understand and learn more on
ITL waits please visit Arup Nanda’s blog below where he clearly explained it with
examples.
