♣
问题
队列等待之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上给出如下的解决方案:
1) Set INITRANS to 50 and pct_free to 40
alter table <table_name> PCTFREE 40 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 40 INITRANS 50;
3、故障分析及解决
目前首先需要找到产生等待事件的表,然后修改其INITRANS和PCTFREE来重构表就可以了。可以查看AWR中的Segments by ITL Waits部分,也可以通过查询DBA_HIST_ACTIVE_SESS_HISTORY视图来获取:
SELECT D.SQL_ID,
CHR(BITAND(P1, -16777216) / 16777215) ||
CHR(BITAND(P1, 16711680) / 65535) "Lock",
BITAND(P1, 65535) "Mode",
D.CURRENT_OBJ#,
COUNT(1),
COUNT(DISTINCT D.SESSION_ID)
FROM DBA_HIST_ACTIVE_SESS_HISTORY D
WHERE D.SAMPLE_TIME BETWEEN
TO_DATE('2016-09-05 16:55:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_DATE('2016-09-05 17:15:00', 'YYYY-MM-DD HH24:MI:SS')
AND D.EVENT = 'enq: TX - allocate ITL entry'
GROUP BY D.SQL_ID,
(CHR(BITAND(P1, -16777216) / 16777215) ||
CHR(BITAND(P1, 16711680) / 65535)),
(BITAND(P1, 65535)),
D.CURRENT_OBJ#;
有了SQL_ID就可以查询其具体的SQL语句了:
SELECT * FROM V$SQL A WHERE A.SQL_ID='1cmnjddakrqbv';
可以看到是一个UPDATE语句,下面通过CURRENT_OBJ#查询具体表名:
SELECT * FROM DBA_OBJECTS D WHERE D.OBJECT_ID=87620;
查看表的属性:
SELECT * FROM DBA_TABLES D WHERE D.TABLE_NAME='ORGANIZATION';
PCT_FREE为10,INI_TRANS为1,根据MOS应该修改这两个值,SQL如下:
ALTER TABLE ORGANIZATION PCTFREE 20 INITRANS 16;
ALTER TABLE ORGANIZATION MOVE;
ALTER INDEX PK_ORGANIZATION REBUILD PCTFREE 20 INITRANS 16 NOLOGGING;
需要注意的是,该表大约2000条记录,很小,所以MOVE的时候可以不用并行,也不用NOLOGGING,若表很大则可以考虑并行和NOLOGGING特性。另外,还需要REBUILD索引才可以。修改完成后,开发人员经过测试后已不再产生该类等待事件了。
另外,若产生该等待事件的会话比较多,并发量比较大,则有可能产生死锁,称为块级别的死锁。有关该部分内容可以参考:http://blog.itpub.net/26736162/viewspace-2124771/。
& 说明:
有关该案例的更多内容可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2124735/、http://blog.itpub.net/26736162/viewspace-2124771/
本文选自《Oracle程序员面试笔试宝典》,作者:小麦苗