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 laterInformation 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:
2) Then re-organize the table using move (alter table move;)
3) Then rebuild all the indexes of this table as below
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.
2) Then re-organize the table using move (alter table service_T move;)
3) Rebuild index
A Combination of increasing both INITRANS and PCTFREE
1) Set INITRANS to 50 and pct_free to 40
2) Re-organize the table using move (alter table move;)
3) Then rebuild all the indexes of the table as below
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 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,免费学习最实用的数据库技术。  |
