enq: TX - allocate ITL entry等待事件深入剖析

IT那活儿 2021-11-13

3834

点击上方“IT那活儿”，关注后了解更多精彩内容！！！

系统配置

oracle数据库 12.2的双节点RAC ，操作系统：AIX Version 7.2

DB patch ：

25294150;

25319173;

29314424;OCW APR 2019 RELEASE UPDATE 12.2.0.1.190416 (29314424)

29314339;Database Apr 2019 Release Update : 12.2.0.1.190416 (29314339)

其他信息暂不统计。

现象分析

节点2在2021-09-01 14:03:39出现异常等待事件enq: TX - allocate ITL entry

等待事件分析

依据等待事件 enq: TX - allocate ITL entry 分析来自Doc ID 1472175.1

导致原因：

默认情况下，表的 INITRANS 值为 1，索引的值为 2。这定义了一个称为感兴趣交易列表 (ITL) 的内部块结构。为了修改一个块中的数据，一个进程需要使用一个空的ITL槽来记录该事务有兴趣修改该块中的一些数据。如果空闲 ITL 插槽不足，则将在块中保留的空闲空间中使用新插槽。如果这用完并且太多并发 DML 事务正在竞争同一个数据块，我们会观察到针对以下等待事件的争用 - “enq：TX - 分配 ITL 条目”。

ITL原理:

ITL(Interested Transaction List)是Oracle数据块内部的一个组成部分，用来记录该块所有发生的事务，一个itl可以看作是一个记录，在一个时间，可以记录一个事务（包括提交或者未提交事务）。当然，如果这个事务已经提交，那么这个itl的位置就可以被反复使用了，因为itl类似记录，所以，有的时候也叫itl槽位。Oracle的每个数据块中都有一个或者多个事务槽，每一个对数据块的并发访问事务都会占用一个事务槽。表和索引的事务槽ini_trans是1、max_trans是255，在oracle10g中，不能修改max_trans这个参数，因为oracle10g忽略了这个参数。如果一个事务一直没有提交，那么，这个事务将一直占用一个itl槽位，itl里面记录了事务信息，回滚段的嵌入口，事务类型等等。如果这个事务已经提交，那么，itl槽位中还保存的有这个事务提交时候的SCN号。如dump一个块，就可以看到itl信息：

Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0006.002.0000158e 0x0080104d.00a1.6e --U- 734 fsc 0x0000.6c9deff00x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

如在并发量特别大的系统中，最好分配足够的itl个数（10g之前的版本），其实它并浪费不了太多的空间，或者，设置足够的pctfree，保证itl能扩展，但是pctfree有可能是被行数据给消耗掉的，如update可能一下占满块空间，所以，也有可能导致块内部的空间不够而导致itl等待，所以在通常情况下，10g版本后引起itl等待的原因往往是因为块的空间不足导致，并不是tran事务槽数量不足，在正常情况下2k的数据块最多可以拥有41个itl，4k数据块最多拥有83，8k最多用友169个itl（以itl 24byte为单位）。INITRANS不足的问题不会出现在索引数据块上，当发现没有足够空间分配ITL slot时，无论是枝点块还是叶子块，数据块会发生分裂(Index Block Split)。

有一种特殊情况也会引起ITL的等待，就是在索引上的递归事务itl争用，这种情况比较特殊。在索引的枝节点上，有且只有一个ITL slot，它是用于当发生节点分裂的递归事务(Recursive Transaction)。在叶子节点上，第一条ITL Slot也是用于分裂的递归事务的。在一个用户事务中，如果发生多次分裂，每一次分裂都是由一个单独的递归事务控制的，如果下层节点分裂导致其父节点分裂，它们的分裂则由同一个递归事务控制。当2个事务同时需要分裂一个枝节点或者叶子节点时，或者枝节点下的2个子节点分别被2个事务分裂，就会造成这种ITL等待。

此问题的主要解决方案是通过重新创建表或索引并更改 INITRANS 或 PCTFREE 参数来增加表或索引的 ITL 功能，以便能够处理更多并发事务。这反过来将有助于减少“enq：TX - 分配 ITL 条目”等待事件。

实验论证

实验一：

数据块上的initrans不能扩展分配slot导致的itl争用测试。

数据块没有空间留给itl slot扩展时候的测试，创建表luda，指定pctfree为0，同时指定initrans为1然后填满相关数据块，再对块满的数据进行更新模拟出itl的等待。

1. 创建表luda，并指定pctfree为0，initrans为1

create table luda(a int) pctfree 0 initrans 1;Table created.

2. 向表中插入数据

idle 06:51:17> beginfor i in 1..20000 loopinsert into luda values(i);end loop;end;/ 2    3    4    5    6PL/SQL procedure successfully completed.commit ;select f,b,count(*) from (select dbms_rowid.rowid_relative_fno(rowid) f,dbms_rowid.rowid_block_number(rowid) b from luda) group by f,b order by 3;F B COUNT(*)---------- ---------- ----------1 94028 1821 94026 7341 94017 7341 94021 7341 94023 7341 93997 7341 93998 7341 94014 7341 94024 7341 93995 7341 94025 7341 94016 7341 94009 7341 94012 7341 94015 7341 93994 7341 93999 7341 94008 7341 94019 7341 94011 7341 94018 7341 94027 7341 93993 7341 94013 7341 94020 7341 94022 7341 93996 7341 94010 734

插入数据后可以发现该表有28个数据块，填满了除了94028块意外的其他数据块。

3. 导出已经填满的数据块93997.

alter system dump datafile 1 block 93997;Block header dump: 0x00416f2dObject id on Block? Yseg/obj: 0x15b03 csc: 0x00.304755 itc: 2 flg: - typ: 1 - DATAfsl: 0 fnx: 0x0 ver: 0x01Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0001.020.0000033c 0x00c0008a.00de.2d --U- 734 fsc 0x0000.003047940x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000bdba: 0x00416f2d

//发现initrans为1的情况下默认是有2个事务槽，itc=2

data_block_dump,data header at 0x7f7c688a4a5c===============tsiz: 0x1fa0hsiz: 0x5cepbl: 0x7f7c688a4a5c76543210flag=--------ntab=1nrow=734frre=-1fsbo=0x5cefseo=0xb95avsp=0x4tosp=0x40xe:pti[0] nrow=734        offs=00x12:pri[0] offs=0x1f990x14:pri[1] offs=0x1f920x16:pri[2] offs=0x1f8b0x18:pri[3] offs=0x1f840x1a:pri[4] offs=0x1f7d0x1c:pri[5] offs=0x1f760x1e:pri[6] offs=0x1f6f0x20:pri[7] offs=0x1f680x22:pri[8] offs=0x1f610x24:pri[9] offs=0x1f5a0x26:pri[10] offs=0x1f530x28:pri[11] offs=0x1f4c0x2a:pri[12] offs=0x1f450x2c:pri[13] offs=0x1f3e0x2e:pri[14] offs=0x1f370x30:pri[15] offs=0x1f300x32:pri[16] offs=0x1f290x34:pri[17] offs=0x1f220x36:pri[18] offs=0x1f1b0x38:pri[19] offs=0x1f140x3a:pri[20] offs=0x1f0d0x3c:pri[21] offs=0x1f060x3e:pri[22] offs=0x1eff0x40:pri[23] offs=0x1ef80x42:pri[24] offs=0x1ef10x44:pri[25] offs=0x1eea0x46:pri[26] offs=0x1ee30x48:pri[27] offs=0x1edc0x4a:pri[28] offs=0x1ed50x4c:pri[29] offs=0x1ece0x4e:pri[30] offs=0x1ec70x50:pri[31] offs=0x1ec00x52:pri[32] offs=0x1eb90x54:pri[33] offs=0x1eb20x56:pri[34] offs=0x1eab0x58:pri[35] offs=0x1ea40x5a:pri[36] offs=0x1e9d0x5c:pri[37] offs=0x1e960x5e:pri[38] offs=0x1e8f0x60:pri[39] offs=0x1e880x62:pri[40] offs=0x1e810x64:pri[41] offs=0x1e7a0x66:pri[42] offs=0x1e730x68:pri[43] offs=0x1e6c0x6a:pri[44] offs=0x1e650x6c:pri[45] offs=0x1e5e0x6e:pri[46] offs=0x1e570x70:pri[47] offs=0x1e500x72:pri[48] offs=0x1e490x74:pri[49] offs=0x1e420x76:pri[50] offs=0x1e3b0x78:pri[51] offs=0x1e340x7a:pri[52] offs=0x1e2d0x7c:pri[53] offs=0x1e260x7e:pri[54] offs=0x1e1f0x80:pri[55] offs=0x1e180x82:pri[56] offs=0x1e110x84:pri[57] offs=0x1e0a0x86:pri[58] offs=0x1e030x88:pri[59] offs=0x1dfc0x8a:pri[60] offs=0x1df50x8c:pri[61] offs=0x1dee0x8e:pri[62] offs=0x1de70x90:pri[63] offs=0x1de10x92:pri[64] offs=0x1dda0x94:pri[65] offs=0x1dd30x96:pri[66] offs=0x1dcc0x98:pri[67] offs=0x1dc50x9a:pri[68] offs=0x1dbe0x9c:pri[69] offs=0x1db70x9e:pri[70] offs=0x1db00xa0:pri[71] offs=0x1da90xa2:pri[72] offs=0x1da20xa4:pri[73] offs=0x1d9b

4. 块满的情况测试slot的分配，根据前面的查询结果我们知道单个块的存储行数为734行，也可以通过dump中的nrow=734得知，所以我们在这部测试中依次更新第100，200，300行的数据。

session 1 更新第100行的数据：

SQL> update luda set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)= 93997 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=100;1 row updated.

session 2更新第200行的数据：

SQL> update luda set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)= 93997 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=200;

session 3更新第300行的数据，先查看此时的SID，并且在执行过程中session 3 hang住：

SQL> select sid from v$mystat where rownum=1;SID----------172SQL> update luda set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)= 93997 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=300;

--此时进程hang住

SQL> select sid,event from v$session where sid=158;SID EVENT---------- ----------------------------------------------------------------172 enq: TX - allocate ITL entry1 row updated.

此时dump次数据块：

alter system dump datafile 1 block 93997；Block header dump: 0x0040ee92Object id on Block? Yseg/obj: 0xcb0a csc: 0x00.bb97e itc: 2 flg: - typ: 1 - DATAfsl: 0 fnx: 0x0 ver: 0x01Itl Xid Uba Flag Lck Scn/Fsc0x01   0x0001.020.0000033c 0x00c0008a.00de.2d ---- 1 fsc 0x0000.003047940x02   0x0000.000.00000000  0x00000000.0000.00 ---- 1 fsc 0x0000.00000000

--通过此时的dump我们也可以发现原先为被占用的2个事务槽已经被占用而且事务未提交。

data_block_dump,data header at 0xd77645c===============tsiz: 0x1fa0hsiz: 0x5cepbl: 0x0d77645cbdba: 0x0040ee9276543210flag=--------ntab=1nrow=734frre=-1fsbo=0x5cefseo=0xbf8avsp=0x4tosp=0x40xe:pti[0] nrow=734 offs=0

从以上验证了空间不足的情况下会导致itl无法分配引起enq: TX – allocate ITL entry等待事件的产生。

实验二：

当一个事务需要修改一个数据块时，需要在数据块头部获取一个可用的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等待。

1. 问题模拟：

create table ttitl as select * from dba_objects;select t.object_id,t.object_name,dbms_rowid.rowid_relative_fno(t.rowid),dbms_rowid.rowid_block_number(t.rowid) from ttitl t where dbms_rowid.rowid_block_number(t.rowid)=143612;

通过几个更新语句将默认的ITL槽占满

update ttitl set object_name='xxxxxxxxxxx' where object_id=20;alter system dump datafile 4 block 143612;

我们要拿4号文件的143612块做实验，目前块中拥有92行数据，现在还需要看块上还存在多少剩余空间? 答案是通过fseo-fsbo或者bbed得到。

fsbo=0xc8 --=======>>>>>>fsbo代表 Free Space Begin offset 空闲 空间的起始偏仪量fseo=0x342 --=======>>>>>>fseo代表Free Space End offset 空闲空间的结束偏仪量0x342-0xc8=0x27A834-200=634

产生一个事务后

fsbo=0xc8fseo=0x32a0x32a-0xc8=0x262=610634-610=24bytes

也正验证了一个itl槽占24bytes的说法

按照这个方式计算，这个块上还能够存在很多事务，610/24=25,该块上还能增加25个事务呢
现在通过update方式将块上的空闲空间缩小

update ttitl set object_name='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'where object_id>60;

现在通过BBED和重新dump该块发现此块空闲空间已经只剩19bytes了。

[oracle@test ~]$ cat par.txtblocksize=8192listfile=filelist.txtmode=edit[oracle@test ~]$ cat filelist.txt1 /u01/app/oracle/oradata/orcl/system01.dbf 9332326402 /u01/app/oracle/oradata/orcl/mctpsys.dbf 104857603 /u01/app/oracle/oradata/orcl/sysaux01.dbf 6186598404 /u01/app/oracle/oradata/orcl/users01.dbf 22465740805 /u01/app/oracle/oradata/orcl/example01.dbf 1048576006 /u01/app/oracle/oradata/orcl/users02.dbf 524288007 /u01/app/oracle/oradata/orcl/mgmt.dbf 13631488008 /u01/app/oracle/oradata/orcl/mgmt_deepdive.dbf 2097152009 /u01/app/oracle/oradata/orcl/mgmt_ecm_depot1.dbf 4194304010 /u01/app/oracle/oradata/orcl/EPMRANGE1.dbf 644245094411 /u01/app/oracle/oradata/orcl/EPMIDX.dbf 429496729612 /u01/app/oracle/oradata/orcl/EPMDAT1.dbf 20971520013 /u01/app/oracle/oradata/orcl/undotbs02.dbf 536870912014 /u01/app/oracle/oradata/orcl/mctpsys1.dbf 31457280015 /u01/app/oracle/oradata/orcl/mctpsys2.dbf 107374182416 /u01/app/oracle/oradata/orcl/rmantbs.dbf 20971520017 /u01/app/oracle/oradata/orcl/ggs1.dbf 52428800018 /u01/app/oracle/oradata/orcl/ZZZ1.DBF 20971520[oracle@test ~]$ bbed parfile=par.txtPassword: blockeditBBED> set dba 4,143612     DBA 0x010230fc (16920828 4,143612)BBED> mapFile: /u01/app/oracle/oradata/orcl/users01.dbf (4)Block: 143612                                Dba:0x010230fc------------------------------------------------------------KTB Data Block (Table/Cluster)struct kcbh, 20 bytes @0 struct ktbbh, 120 bytes @20 struct kdbh, 14 bytes @148 struct kdbt[1], 4 bytes @162 sb2 kdbr[91] @166 ub1 freespace[19] @348 --====>>>>>>>>>>>>>块上的空闲空间为19bytes ub1 rowdata[7821] @367 ub4 tailchk @8188

--摘自datafile dump日志

fsbo=0xc8fseo=0xdb0xdb-0xc8=0x13=19

这块上只有19bytes字节的空间了，看来是无法再容纳一个ITL槽了，再新产生一个事务

update ttitl set object_name='I_FILE1' where object_id=41;

此时可以看到enq: TX - allocate ITL entry等待，直至块上的其它事务提交或回滚后，此会话才能继续，否则一直处于等待状态。

SQL> select sid,serial#,status,username,event,seconds_in_wait,sql_id from v$session where serial#<>1 and sql_id is not null and event not like '%SQL*Net message%' and event not like 'Streams AQ%' order by 7;       SID    SERIAL# STATUS   USERNAME   EVENT                        SECONDS_IN_WAIT SQL_ID---------- ---------- -------- ---------- ---------------------------------------- --------------- -------------       528     39292 ACTIVE   TT       enq: TX - allocate ITL entry                    4 512zw5fc3bztt

--记录一下enq锁的p1 p2 p3值的含义

select * from v$session where event like 'enq%';EVENT# 189EVENT enq: TX - allocate ITL entryP1TEXT name|modeP1 1415053316P1RAW 0000000054580004P2TEXT usn<<16 | slotP2 131084P2RAW 000000000002000CP3TEXT sequenceP3 88864P3RAW 0000000000015B20

--P1值与锁名称和锁模式有关1415053316转换成16进制后为54580004,其中54代表字母T,58代表字母X,合一起就是锁的name。

SQL> select dump('T',16),dump('X',16) from dual;DUMP('T',16) DUMP('X',16)---------------- ----------------Typ=96 Len=1: 54 Typ=96 Len=1: 58

--P1值的后4位0004代表申请锁的模式

SQL> select * from v$lock where sid=528;ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------00000000DEC35368 00000000DEC35388 528 TX 131084      88864          0          4        129          000000000DD5099A8 00000000DD5099D0 528 TM 1519283          0          3          0       1294          0

--P2和P3值与事务相关，比如上面的P2值131084代表XIDUSN和XIDSLOT，

select 131084/45536 as usn,round(mod(131084,45536)) slot from dual;

--P3值88864代表xidsqn

ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_TIME START_SCNB START_SCNW START_UEXT START_UBAFIL START_UBABLK START_UBASQN START_UBAREC SES_ADDR FLAG SPACE RECURSIVE NOUNDO PTX NAME PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN DSCN-B DSCN-W USED_UBLK USED_UREC LOG_IO PHY_IO CR_GET CR_CHANGE START_DATE DSCN_BASE DSCN_WRAP START_SCN DEPENDENT_SCN XID PRV_XID PTX_XID---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ---------- ------------ ------------ ------------ ------------ ---------------- ---------- ----- --------- ------ --- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ------------- ---------------- ---------------- ----------------00000000DD61F730 6         29      74079         19       9504      19040         51 ACTIVE 04/20/15 10:30:56    3585075880       2902         18           19         9504        19040           51 00000000DF221CA0 7683 NO    NO        NO     NO                                                                                            0          0          0          0          0          0          0   &

对于ITL事务槽来说，除去其初始分配的事务槽（可以理解为固定存在的事务槽），在一个BLOCK写满的情况下，继续扩展ITL事务槽需要占用PCTFREE参数指定的预留空间。例如PCTFREE值设定为10%，而一个块的大小是8KB也就是8192B，则预留的空间约为819.2B，而一个ITL事务槽的大小为24B（ITL事务槽大小参考文献，最开始查看一些文章有说大小是46B的，但是实际测试不相符），由此我们可以大致推断其最多可扩展的ITL事务槽数量。而在实际生产环境中，update语句更新该数据块中的字段，使其字段长度增加，也会占用PCTFREE预留空间,导致ITL事务槽的可扩展数量减少。

从根本上来说enq: TX - allocate ITL entry等待事件的产生是由于当前BLOCK没有足够的空间去扩展事务槽，或者由于多个长事务导致的ITL事务槽长时间占用，从而引起事务槽争用。

2. 一般常用解决办法：

Increase INITRANS

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:

alter table <table name> INITRANS 50;

2) Then re-organize the table using move (alter table <table_name> move;)

3) Then rebuild all the indexes of this table as below

alter index <index_name> rebuild INITRANS 50;

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.

1) Spreading rows into more number of blocks will also helps to reduce this wait event.

alter table <table name> PCTFREE 20;

2) Then re-organize the table using move (alter table <table_name> move;)

3) Rebuild index alter index index_name rebuild PCTFREE 20;

A Combination of increasing both INITRANS and PCTFREE

1) Set INITRANS to 50 and pct_free to 20 alter table <table_name> PCTFREE 20 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 20 INITRANS 50;

END

更多精彩干货分享

点击下方名片关注

IT那活儿

数据库

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

enq: TX - allocate ITL entry等待事件深入剖析

评论