互联网服务Oracle数据库

enq: TX - allocate ITL entry持续走高?

QQ截图20180419170234.png

QQ截图20180419170234.png

参与4

1同行回答

chuan717chuan717  数据库管理员 , 51
CAUSEBy 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 t...显示全部

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:

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;

总结一下:

原因: 表和索引的默认INITRANS值不合适,引起的事务槽分配等待。 当一个事务需要修改一个数据块时,需要在数据块头部获取一个可用的 ITL 槽,用于记录事务的id, 使用undo数据块地址,scn等信息。如果事务申请不到新的可用ITL槽时,就会产生enq: TX - allocate ITL entry等待。 发生这个等待时,要么是块上的已分配ITL个数(通过ini_trans参数控制)达到了上限255(10g以后没有了max_trans限制参数,无法指定小于255的值),要么是这个块中没有更多的空闲空间来容纳一个ITL了(每个ITL占用24bytes)。 默认情况下创建的表ITL槽数最小为1+1,pctfree为10,那么如果是这样一种情况,如果表中经常执行update语句,然后块中剩余的10%空间所剩无几,而且业务的并发量还很大,此时就很容易遇到enq: TX - allocate ITL entry等待。

解决:解决方式就是调整表和索引的INITRANS,有必要还需要调整 pcfree 值。

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;

收起
互联网服务 · 2019-11-20
浏览1322

提问者

kevin898
测试工程师捷越联合信息咨询服务有限公司

相关问题

相关资料

相关文章

问题状态

  • 发布时间:2018-04-19
  • 关注会员:2 人
  • 问题浏览:1956
  • 最近回答:2019-11-20
  • X社区推广