點擊上方“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