最近發現有個RAC庫後臺alert日誌中每天都會報出大量全域性死鎖，並且在AWR中發現TOP5事件中總是存在大量enq: TX - allocate ITL entry等待

如下：
       SID    SERIAL# STATUS   USERNAME   PROGRAM              EVENT                                    SECONDS_IN_WAIT BLOCKING_SESSION SQL_ID
---------- ---------- -------- ---------- -------------------- ---------------------------------------- --------------- ---------------- -------------
      3085      50398 ACTIVE   ECMS       Aloha.exe            enq: TX - allocate ITL entry                          28             2826 bhwrndpu3jvpg
      1589      56075 ACTIVE   ECMS       Aloha.exe            enq: TX - allocate ITL entry                          28             3088 bhwrndpu3jvpg
      1833       3652 ACTIVE   ECMS       Aloha.exe            enq: TX - row lock contention                         94             3138 bhwrndpu3jvpg
      2817      50181 ACTIVE   ECMS       Aloha.exe            enq: TX - row lock contention                         94             3088 bhwrndpu3jvpg

問題分析，發生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等待。

問題模擬：
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的說法

按照這個方式計算，這個塊上還能夠存在很多事務，610/24=25,該塊上還能增加25個事務呢
現在透過update方式將塊上的空閒空間縮小
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: Release 2.0.0.0.0 - Limited Production on Mon Apr 20 11:37:30 2015

Copyright (c) 1982, 2007, Oracle. All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************
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槽了，再新產生一個事務
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#     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;

--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   &

enq: TX - allocate ITL entry等待過多導致全域性死鎖

相關文章