今天在分析一份awr中发现了较为明显的enq: TX – allocate ITL entry等待,这里通过试验详细重现了enq: TX – allocate ITL entry等待
创建测试对象
SQL> create table t_xifenfei (name char(2000)) pctfree 0 initrans 1;
Table created.
SQL> insert into t_xifenfei select object_name from all_objects where rownum < 5;
4 rows created.
SQL> commit;
Commit complete.
SQL> alter system flush buffer_cache;
System altered.
SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) file#,
2 dbms_rowid.rowid_block_number(rowid) block# from t_xifenfei;
FILE# BLOCK#
---------- ----------
4 32
bbed查看block
BBED> set block 32
BLOCK# 32
BBED> map
File: /u01/oracle/oradata/XFF/users01.dbf (0)
Block: 32 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[4] @118
ub1 freespace[38] @126 --该block空闲空间为38byte
ub1 rowdata[8024] @164
ub4 tailchk @8188
BBED> p ktbbh
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x0000d318
ub4 ktbbhod1 @24 0x0000d318
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0xc0320e3b
ub2 kscnwrp @32 0x0b2c
b2 ktbbhict @36 2
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x01000019
struct ktbbhitl[0], 24 bytes @44 --1个itl slot为24byte
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0015
ub2 kxidslt @46 0x0019
ub4 kxidsqn @48 0x00000005
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x0080009d
ub2 kubaseq @56 0x0002
ub1 kubarec @58 0x28
ub2 ktbitflg @60 0x2004 (KTBFUPB)
union _ktbitun, 2 bytes @62
b2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0xc0320e4e
struct ktbbhitl[1], 24 bytes @68 --有两个itl slot
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0000
ub2 kxidslt @70 0x0000
ub4 kxidsqn @72 0x00000000
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00000000
ub2 kubaseq @80 0x0000
ub1 kubarec @82 0x00
ub2 ktbitflg @84 0x0000 (NONE)
union _ktbitun, 2 bytes @86
b2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
通过bbed我们可以得出如下结论：
1.该block剩余38 byte 空闲空间可以用来存放数据
2.该block 初始化 itl 为2(和我们设置的1不相符)
3.一个itl slot为24byte
更新表记录
--session 1 SQL> select trim(name) from t_xifenfei; TRIM(NAME) -------------------------------------------------------------------------------- ICOL$ I_USER1 CON$ UNDO$ SQL> update t_xifenfei set name='WWW.XIFENFEI.COM' WHERE name='ICOL$'; 1 row updated. --session 2 SQL> update t_xifenfei set name='www.orasos.com' where name='UNDO$'; 1 row updated. --session 3 SQL> update t_xifenfei set name='www.xifenfei.com' where name='CON$'; 1 row updated. --session 4 SQL> update t_xifenfei set name='www.xifenfei.com' where name='I_USER1'; --hang住 --session 5 SQL> select event from v$session where event like 'enq%'; EVENT ---------------------------------------------------------------- enq: TX - allocate ITL entry
通过这里可以看到我们模拟了4个update 该block操作(均未提交)，前面三个可以正常的update操作，第四个出现了enq: TX – allocate ITL entry等待,根据我们知识分析(未提交事务的itl不能覆盖,一个dml操作需要一个itl),这里使用了3个itl slot,而我们已经知道一个itl 需要24byte，该block初始化有2个itl,现在这里有3个dml操作成功,即占用了3个itl,所以该block的剩余空间只有38-24=14 byte<24byte,因此无法分配第四个itl slot从而出现了enq: TX - allocate ITL entry等待
bbed验证上述分析
BBED> map
File: /u01/oracle/oradata/XFF/users01.dbf (0)
Block: 32 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[4] @142
ub1 freespace[14] @150
ub1 rowdata[8024] @164
ub4 tailchk @8188
BBED> p ktbbh
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x0000d318
ub4 ktbbhod1 @24 0x0000d318
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0xc0320eb0
ub2 kscnwrp @32 0x0b2c
b2 ktbbhict @36 3
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x01000019
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0003
ub2 kxidslt @46 0x001f
ub4 kxidsqn @48 0x00000208
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00800027
ub2 kubaseq @56 0x0414
ub1 kubarec @58 0x01
ub2 ktbitflg @60 0x0001 (NONE)
union _ktbitun, 2 bytes @62
b2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x00000000
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x000f
ub4 kxidsqn @72 0x00000185
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x0080008a
ub2 kubaseq @80 0x01a6
ub1 kubarec @82 0x0c
ub2 ktbitflg @84 0x0001 (NONE)
union _ktbitun, 2 bytes @86
b2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
struct ktbbhitl[2], 24 bytes @92
struct ktbitxid, 8 bytes @92
ub2 kxidusn @92 0x0008
ub2 kxidslt @94 0x002a
ub4 kxidsqn @96 0x00000217
struct ktbituba, 8 bytes @100
ub4 kubadba @100 0x008000cc
ub2 kubaseq @104 0x0291
ub1 kubarec @106 0x12
ub2 ktbitflg @108 0x0001 (NONE)
union _ktbitun, 2 bytes @110
b2 _ktbitfsc @110 0
ub2 _ktbitwrp @110 0x0000
ub4 ktbitbas @112 0x00000000
可以看到剩余空间为14byte，事务槽为3个，因此上述分析为正确。
提交会话测试
--session 1 SQL> commit; Commit complete. --session 4 SQL> update t_xifenfei set name='www.xifenfei.com' where name='I_USER1'; 1 row updated.
证明commit掉事务后,itl slot可以重利用
总结说明
enq: TX – allocate ITL entry为分配ITL条目的等待，因为PCTFREE不足，BLOCK中没有足够空间分配ITL，ORACLE只能重用ITL，但是这个时候由于没有COMMIT，无法重用ITL，所以会出现allocate ITL等待事件。要解决此类问题，我们可以考虑增加PCTFREE和initrans大小,需要注意该修改只能对于新block生效,已经存放数据的block不会发生改变.另外可以考虑修改业务逻辑,减少频繁访问
