enq: TX – allocate ITL entry in Oracle database and Goldengate Replicate Process

Hi,

You can see hight waits for event enq: TX – allocate ITL entry in AWR report and Goldengate Replicate process.

Goldengate replicate process has 2 hours lag, and it was increasing.

When I review Goldengate process, I saw the enq: TX – allocate ITL entry in the Oracle database.

When I check the AWR Reports,  Observe high waits for event enq: TX – allocate ITL entry

Top 5 Timed Foreground Events

Event                             Waits        Time(s) Avg wait (ms) % DB time   Wait Class
enq: TX - allocate ITL entry     1,200         3,129      2607       85.22       Configuration
DB CPU                                           323                  8.79
gc buffer busy acquire           17,261           50        3         1.37        Cluster
gc cr block 2-way                143,108          48        0         1.32        Cluster
gc current block busy            10,631           46        4         1.24        Cluster

If you don’t know how to generate and Read and Analyze an AWR report, then read the following post.

How to Read or Analyze an AWR Report in Oracle -4 | TOP SQL

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:

Increase INITRANS

1) Depending on the number of transactions in the table we need to alter the value of INITRANS. here it has been changed to 50:

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;

Increase PCTFREE

If the issue is not resolved by increasing INITRANS then try increasing PCTFREE. Increasing PCTFREE holds more space back and so spreads the same number of rows over more blocks. This means that there are more ITL slots available, overall.

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

alter table <table name>  PCTFREE 20;

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

3) Rebuild index

alter index index_name  rebuild PCTFREE 20;

A Combination of increasing both INITRANS and PCTFREE

1) Set INITRANS to 50 and pct_free to 20

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;

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database