点击上方“IT那活儿”,关注后了解更多精彩内容!!!
oracle数据库 12.2的双节点RAC ,操作系统:AIX Version 7.2
节点2在2021-09-01 14:03:39出现异常等待事件enq: TX - allocate ITL entry
依据等待事件 enq: TX - allocate ITL entry 分析来自Doc ID 1472175.1
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.002.0000158e 0x0080104d.00a1.6e --U- 734 fsc 0x0000.6c9deff0
0x02 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)。
实验一:
create table luda(a int) pctfree 0 initrans 1;
Table created.
2. 向表中插入数据
idle 06:51:17> begin
for i in 1..20000 loop
insert into luda values(i);
end loop;
end;
/ 2 3 4 5 6
PL/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 182
1 94026 734
1 94017 734
1 94021 734
1 94023 734
1 93997 734
1 93998 734
1 94014 734
1 94024 734
1 93995 734
1 94025 734
1 94016 734
1 94009 734
1 94012 734
1 94015 734
1 93994 734
1 93999 734
1 94008 734
1 94019 734
1 94011 734
1 94018 734
1 94027 734
1 93993 734
1 94013 734
1 94020 734
1 94022 734
1 93996 734
1 94010 734
alter system dump datafile 1 block 93997;
Block header dump: 0x00416f2d
Object id on Block? Y
seg/obj: 0x15b03 csc: 0x00.304755 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.020.0000033c 0x00c0008a.00de.2d --U- 734 fsc 0x0000.00304794
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x00416f2d
//发现initrans为1的情况下默认是有2个事务槽,itc=2
data_block_dump,data header at 0x7f7c688a4a5c
===============
tsiz: 0x1fa0
hsiz: 0x5ce
pbl: 0x7f7c688a4a5c
76543210
flag=--------
ntab=1
nrow=734
frre=-1
fsbo=0x5ce
fseo=0xb95
avsp=0x4
tosp=0x4
0xe:pti[0] nrow=734 offs=0
0x12:pri[0] offs=0x1f99
0x14:pri[1] offs=0x1f92
0x16:pri[2] offs=0x1f8b
0x18:pri[3] offs=0x1f84
0x1a:pri[4] offs=0x1f7d
0x1c:pri[5] offs=0x1f76
0x1e:pri[6] offs=0x1f6f
0x20:pri[7] offs=0x1f68
0x22:pri[8] offs=0x1f61
0x24:pri[9] offs=0x1f5a
0x26:pri[10] offs=0x1f53
0x28:pri[11] offs=0x1f4c
0x2a:pri[12] offs=0x1f45
0x2c:pri[13] offs=0x1f3e
0x2e:pri[14] offs=0x1f37
0x30:pri[15] offs=0x1f30
0x32:pri[16] offs=0x1f29
0x34:pri[17] offs=0x1f22
0x36:pri[18] offs=0x1f1b
0x38:pri[19] offs=0x1f14
0x3a:pri[20] offs=0x1f0d
0x3c:pri[21] offs=0x1f06
0x3e:pri[22] offs=0x1eff
0x40:pri[23] offs=0x1ef8
0x42:pri[24] offs=0x1ef1
0x44:pri[25] offs=0x1eea
0x46:pri[26] offs=0x1ee3
0x48:pri[27] offs=0x1edc
0x4a:pri[28] offs=0x1ed5
0x4c:pri[29] offs=0x1ece
0x4e:pri[30] offs=0x1ec7
0x50:pri[31] offs=0x1ec0
0x52:pri[32] offs=0x1eb9
0x54:pri[33] offs=0x1eb2
0x56:pri[34] offs=0x1eab
0x58:pri[35] offs=0x1ea4
0x5a:pri[36] offs=0x1e9d
0x5c:pri[37] offs=0x1e96
0x5e:pri[38] offs=0x1e8f
0x60:pri[39] offs=0x1e88
0x62:pri[40] offs=0x1e81
0x64:pri[41] offs=0x1e7a
0x66:pri[42] offs=0x1e73
0x68:pri[43] offs=0x1e6c
0x6a:pri[44] offs=0x1e65
0x6c:pri[45] offs=0x1e5e
0x6e:pri[46] offs=0x1e57
0x70:pri[47] offs=0x1e50
0x72:pri[48] offs=0x1e49
0x74:pri[49] offs=0x1e42
0x76:pri[50] offs=0x1e3b
0x78:pri[51] offs=0x1e34
0x7a:pri[52] offs=0x1e2d
0x7c:pri[53] offs=0x1e26
0x7e:pri[54] offs=0x1e1f
0x80:pri[55] offs=0x1e18
0x82:pri[56] offs=0x1e11
0x84:pri[57] offs=0x1e0a
0x86:pri[58] offs=0x1e03
0x88:pri[59] offs=0x1dfc
0x8a:pri[60] offs=0x1df5
0x8c:pri[61] offs=0x1dee
0x8e:pri[62] offs=0x1de7
0x90:pri[63] offs=0x1de1
0x92:pri[64] offs=0x1dda
0x94:pri[65] offs=0x1dd3
0x96:pri[66] offs=0x1dcc
0x98:pri[67] offs=0x1dc5
0x9a:pri[68] offs=0x1dbe
0x9c:pri[69] offs=0x1db7
0x9e:pri[70] offs=0x1db0
0xa0:pri[71] offs=0x1da9
0xa2:pri[72] offs=0x1da2
0xa4:pri[73] offs=0x1d9b
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.
SQL> update luda set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)= 93997 and
dbms_rowid.ROWID_ROW_NUMBER(rowid)=200;
SQL> select sid from v$mystat where rownum=1;
SID
----------
172
SQL> update luda set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)= 93997 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=300;
SQL> select sid,event from v$session where sid=158;
SID EVENT
---------- ----------------------------------------------------------------
172 enq: TX - allocate ITL entry
1 row updated.
alter system dump datafile 1 block 93997;
Block header dump: 0x0040ee92
Object id on Block? Y
seg/obj: 0xcb0a csc: 0x00.bb97e itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.020.0000033c 0x00c0008a.00de.2d ---- 1 fsc 0x0000.00304794
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 1 fsc 0x0000.00000000
data_block_dump,data header at 0xd77645c
===============
tsiz: 0x1fa0
hsiz: 0x5ce
pbl: 0x0d77645c
bdba: 0x0040ee92
76543210
flag=--------
ntab=1
nrow=734
frre=-1
fsbo=0x5ce
fseo=0xbf8
avsp=0x4
tosp=0x4
0xe:pti[0] nrow=734 offs=0
从以上验证了空间不足的情况下会导致itl无法分配引起enq: TX – allocate ITL entry等待事件的产生。
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=0x27A
834-200=634
产生一个事务后
fsbo=0xc8
fseo=0x32a
0x32a-0xc8=0x262=610
634-610=24bytes
也正验证了一个itl槽占24bytes的说法
update ttitl set
object_name=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
where object_id>60;
现在通过BBED和重新dump该块发现此块空闲空间已经只剩19bytes了。
[oracle@test ~]$ cat par.txt
blocksize=8192
listfile=filelist.txt
mode=edit
[oracle@test ~]$ cat filelist.txt
1 /u01/app/oracle/oradata/orcl/system01.dbf 933232640
2 /u01/app/oracle/oradata/orcl/mctpsys.dbf 10485760
3 /u01/app/oracle/oradata/orcl/sysaux01.dbf 618659840
4 /u01/app/oracle/oradata/orcl/users01.dbf 2246574080
5 /u01/app/oracle/oradata/orcl/example01.dbf 104857600
6 /u01/app/oracle/oradata/orcl/users02.dbf 52428800
7 /u01/app/oracle/oradata/orcl/mgmt.dbf 1363148800
8 /u01/app/oracle/oradata/orcl/mgmt_deepdive.dbf 209715200
9 /u01/app/oracle/oradata/orcl/mgmt_ecm_depot1.dbf 41943040
10 /u01/app/oracle/oradata/orcl/EPMRANGE1.dbf 6442450944
11 /u01/app/oracle/oradata/orcl/EPMIDX.dbf 4294967296
12 /u01/app/oracle/oradata/orcl/EPMDAT1.dbf 209715200
13 /u01/app/oracle/oradata/orcl/undotbs02.dbf 5368709120
14 /u01/app/oracle/oradata/orcl/mctpsys1.dbf 314572800
15 /u01/app/oracle/oradata/orcl/mctpsys2.dbf 1073741824
16 /u01/app/oracle/oradata/orcl/rmantbs.dbf 209715200
17 /u01/app/oracle/oradata/orcl/ggs1.dbf 524288000
18 /u01/app/oracle/oradata/orcl/ZZZ1.DBF 20971520
[oracle@test ~]$ bbed parfile=par.txt
Password: blockedit
BBED> set dba 4,143612
DBA 0x010230fc (16920828 4,143612)
BBED> map
File: /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=0xc8
fseo=0xdb
0xdb-0xc8=0x13=19
这块上只有19bytes字节的空间了,看来是无法再容纳一个ITL槽了,再新产生一个事务
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
select * from v$session where event like enq%;
EVENT# 189
EVENT enq: TX - allocate ITL entry
P1TEXT name|mode
P1 1415053316
P1RAW 0000000054580004
P2TEXT usn<<16 | slot
P2 131084
P2RAW 000000000002000C
P3TEXT sequence
P3 88864
P3RAW 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 0
00000000DD5099A8 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;
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 &
Increase INITRANS