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.

 

 

 

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;

 

NOTE: The table/index can be altered to set the new value for INITRANS. But the altered value takes effect for new blocks only. 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

 

 

 

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

 

 87 views last month,  1 views today

Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply