【MOS】 Troubleshooting waits for enq: TX - allocate ITL entry(1472175.1)

Troubleshooting waits for 'enq: TX - allocate ITL entry' (文档 ID 1472175.1)

In this Document

Symptoms
Cause
Solution
  Increase INITRANS
  Increase PCTFREE
  A Combination of increasing both INITRANS and PCTFREE
References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS

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

CAUSE

By default INITRANS value for table is 1 and for index is 2. This defines an internal block structure called the Interested Transaction List (ITL). In order to modify data in a block, a process needs to use an empty ITL slot to record that the transaction is interested in modifying some of the data in the block. If there are insufficient free ITL slots then new ones will be taken in the free space reserved in the block. If this runs out and too many concurrent DML transactions are competing for the same data block we observe contention against the following wait event - "enq: TX - allocate ITL entry".

You can see candidates for re-organisation due to ITL problems in the "Segments by ITL Waits"  section of an Automatic Workload Repository (AWR) report:

Segments by ITL Waits

  * % of Capture shows % of ITL waits for each top segment compared
  * with total ITL waits for all segments captured by the Snapshot

Owner Tablespace Name Object Name Subobject Name Obj. Type       ITL  Waits % of Capture
PIN   BRM_TABLES      SERVICE_T                  TABLE           188               84.30
PIN   BRM_TABLES      BILLINFO_T  P_R_06202012   TABLE PARTITION  35               15.70

SOLUTION

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

A)

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 tableINITRANS 50;

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

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

alter index 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 :
B)

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

alter table

  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;

A Combination of increasing both INITRANS and PCTFREE

1) Set INITRANS to 50 and  pct_free to 40

alter table PCTFREE 40  INITRANS 50;

2) Re-organize the table using move (alter table move;)

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

alter index  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. 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

You can find information relating to other 'enq: TX - ...' variants in the following articles:

Document 1476298.1 Resolving Issues Where 'enq: TX - row lock contention' Waits are Occurring 
Document 873243.1 Troubleshooting 'enq: TX - index contention' Waits in a RAC Environment. 
Document 1946502.1 Resolving Issues Where 'enq: TX - contention' Waits are Occurring

About Me


..........................................................................................................................................................................................................................................................................................................

● 本文来自于MOS转载文章,(文档 ID 1472175.1)

● 本文在itpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● QQ群:230161599  微信群:私聊

● 小麦苗分享的其它资料:http://blog.itpub.net/26736162/viewspace-1624453/

● 小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群: 230161599   微信群:私聊

● 联系我请加QQ好友(642808185),注明添加缘由

●【版权所有,文章允许转载,但须以链接方式注明源地址,否则追究法律责任】

..........................................................................................................................................................................................................................................................................................................

手机长按下图识别二维码或微信客户端扫描下边的二维码来关注小麦苗的微信公众号:xiaomaimiaolhr,免费学习最实用的数据库技术。

时间: 2024-10-12 17:59:06

【MOS】 Troubleshooting waits for enq: TX - allocate ITL entry(1472175.1)的相关文章

关于enq: TX - allocate ITL entry的问题分析

今天发现系统在下午1点左右的时候负载比较高,就抓取了一个最新的awr报告. Snap Id Snap Time Sessions Cursors/Session Begin Snap: 20892 26-Nov-14 13:20:17 3623 5.4 End Snap: 20893 26-Nov-14 13:30:17 3602 5.4 Elapsed:   10.01 (mins)     DB Time:   365.56 (mins)     来看看是否有top的等待事件,对于其它的几个

oracle数据库enq: TX - allocate ITL entry性能诊断

朋友公司的某铁路集团医保系统出现性能问题业务不能正常办理,下面是出现性能问题时的awr报告 从等待事件来看主要是出现了多处锁竞争.其中enq: TX - allocate ITL entry等待事件是由于缺省情况下创建的表的INITRANS参数为1,索引的INITRANS参数值为2.当有太多的并发DML操作同时操作相同的数据块或索引块就会出现这个等待事件,可以通过查看Segments by ITL Waits部分的信息来了解出现大量并发DML操作的对象 从下面的信息可以看出消耗时间最长的语句都是

[20150721]enq TX - allocate ITL entry

[20150721]enq TX - allocate ITL entry.txt --昨天我做了一个测试链接: http://blog.itpub.net/267265/viewspace-1742243/ --本想通过这个例子说明为什么8K数据块Hakan Factor=736? --晚上我想到一种这种特殊的表会不会产生enq TX - allocate ITL entry,也就是itl不足的情况. 1.建立测试环境: SCOTT@test> @ &r/ver1 PORT_STRING 

【故障处理】队列等待之TX - allocate ITL entry案例

[故障处理]队列等待之TX - allocate ITL entry案例 1  BLOG文档结构图       2  前言部分 2.1  导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① enq: TX - allocate ITL entry等待事件的解决 ② 一般等待事件的解决办法 ③ 队列等待的基本知识 Tips: ① 本文在ITpub(http://blog.itpub.net/26736162).博客园(ht

【MOS】Troubleshooting Performance Issues (文档 ID 1377446.1)

[MOS]Troubleshooting Performance Issues (文档 ID 1377446.1) In this Document Purpose   Best Practices   Pro-Active Problem Avoidance and Diagnostic Collection   Performance Service Request Diagnostic Collection (SRDC) documents Troubleshooting Steps  

【故障处理】队列等待之enq: TX - row lock contention

[故障处理]队列等待之enq: TX - row lock contention 1  BLOG文档结构图   2  前言部分 2.1  导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① enq: TX - row lock contention等待事件的解决 ② 一般等待事件的解决办法 ③ 队列等待的基本知识 ④ ADDM的使用 ⑤ 如何获取历史执行计划 ⑥ 查询绑定变量的具体值 ⑦ 很多有用的查询性能的SQL语句

【MOS】Troubleshooting 'enq: TX - index contention' Waits (文档 ID 873243.1)

                                                                                                                                                                                                                                                          

【MOS】常见问题cursor library cache类型的等待事件

[MOS]常见问题:'cursor:mutex ..'/ 'cursor:pin ..'/ 'library cache:mutex ..'类型的等待事件 (文档 ID 1525791.1) 文档内容 用途 问题和答案   什么是 'cursor: ' 等待事件?   最常见的等待事件是什么?   等待事件最常见的原因是什么?   如何避免这些等待事件?   可以在什么位置找到原因诊断以及关于这些等待事件的更多信息?   有用参考 参考 适用于: Oracle Database - Enterp

【故障处理】队列等待之enq IV - contention案例

[故障处理]队列等待之enq IV -  contention案例 1.1  BLOG文档结构图   1.2  前言部分 1.2.1  导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① 队列等待之enq IV -  contention案例(重点) Tips: ① 本文在itpub(http://blog.itpub.net/26736162).博客园(http://www.cnblogs.com/lhrbest)和微