【DB笔试面试866】队列等待之enq: TX - allocate ITL entry

 

♣问题

队列等待之enq: TX - allocate ITL entry

 

     ♣答案

 

1、故障环境介绍

项目	Source db
db类型	RAC
db version	11.2.0.3.0
db存储	ASM
OS版本及kernel版本	AIX 64位7.1.0.0

2、故障发生现象及报错信息

AWR的头部如下所示：

Elapsed时间为20分钟，而DB Time为11461分钟，负载很大，很可能有异常的等待事件。每秒的事务数为349.9，比较大，下面查看等待事件：

AWR的其它部分就不分析了，首先这个等待事件“enq: TX - allocate ITL entry”在MOS（Troubleshooting waits for 'enq: TX - allocate ITL entry' (文档 ID 1472175.1)）中有详细介绍。

当一个事务需要修改一个数据块时，需要在数据块头部获取一个可用的ITL槽，用于记录事务的ID、使用Undo数据块地址以及SCN等信息。如果事务申请不到新的可用ITL槽时，就会产生enq: TX - allocate ITL entry等待。当发生这个等待时，要么是块上的已分配ITL个数达到了上限255，要么是这个块中没有更多的空闲空间来容纳一个ITL了。在默认情况下创建的表ITL槽数最小为2，PCTFREE为10。如果表中经常执行UPDATE语句，那么就会导致块中剩余的10%空间所剩无几，而且在业务的并发量很大的情况下，此时就更容易遇到enq: TX - allocate ITL entry等待。

该等待事件的解决方式就是调整表和索引的INITRANS值，还需要调整PCTFREE值。MOS上给出如下的解决方案：

11) Set INITRANS to 50 and  pct_free to 40
2alter table <table_name> PCTFREE 40  INITRANS 50;
32) Re-organize the table using move (alter table <table_name> move;)
43) Then rebuild all the indexes of the table as below
5alter index <index_name>  rebuild PCTFREE 40 INITRANS 50;

3、故障分析及解决

目前首先需要找到产生等待事件的表，然后修改其INITRANS和PCTFREE来重构表就可以了。可以查看AWR中的Segments by ITL Waits部分，也可以通过查询DBA_HIST_ACTIVE_SESS_HISTORY视图来获取：

 1 SELECT D.SQL_ID,
 2        CHR(BITAND(P1, -16777216) / 16777215) ||
 3        CHR(BITAND(P1, 16711680) / 65535) "Lock",
 4        BITAND(P1, 65535) "Mode",
 5        D.CURRENT_OBJ#,
 6        COUNT(1),
 7        COUNT(DISTINCT D.SESSION_ID)
 8   FROM DBA_HIST_ACTIVE_SESS_HISTORY D
 9  WHERE D.SAMPLE_TIME BETWEEN
10        TO_DATE('2016-09-05 16:55:00', 'YYYY-MM-DD HH24:MI:SS') AND
11        TO_DATE('2016-09-05 17:15:00', 'YYYY-MM-DD HH24:MI:SS')
12    AND D.EVENT = 'enq: TX - allocate ITL entry'
13  GROUP BY D.SQL_ID,
14           (CHR(BITAND(P1, -16777216) / 16777215) ||
15           CHR(BITAND(P1, 16711680) / 65535)),
16           (BITAND(P1, 65535)),
17           D.CURRENT_OBJ#;

有了SQL_ID就可以查询其具体的SQL语句了：

1SELECT * FROM V$SQL A WHERE A.SQL_ID='1cmnjddakrqbv'; 

 

可以看到是一个UPDATE语句，下面通过CURRENT_OBJ#查询具体表名：

1SELECT * FROM DBA_OBJECTS D WHERE D.OBJECT_ID=87620;

 

查看表的属性：

 

1SELECT * FROM DBA_TABLES D WHERE D.TABLE_NAME='ORGANIZATION'; 

PCT_FREE为10，INI_TRANS为1，根据MOS应该修改这两个值，SQL如下：

1ALTER TABLE ORGANIZATION PCTFREE 20  INITRANS 16;
2ALTER TABLE ORGANIZATION MOVE;
3ALTER INDEX PK_ORGANIZATION  REBUILD PCTFREE 20 INITRANS 16 NOLOGGING;

 

需要注意的是，该表大约2000条记录，很小，所以MOVE的时候可以不用并行，也不用NOLOGGING，若表很大则可以考虑并行和NOLOGGING特性。另外，还需要REBUILD索引才可以。修改完成后，开发人员经过测试后已不再产生该类等待事件了。