enq: TX - allocate ITL entry等待事件分析

chenoracle發表於2020-12-20
ENQ事件

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號。
如果在併發量特別大的系統中，最好分配足夠的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)。

模擬場景：initrans不能擴充套件分配slot導致的itl爭用。

資料塊上的initrans不能擴充套件分配slot導致的itl爭用測試：

建立表cjc_t1，預設INI_TRANS為1，MAX_TRANS為255，PCT_FREE為10。 

create table cjc_t1(a int);
set line 300
set pagesize 100
select dbms_metadata.get_ddl('TABLE','CJC_T1','CJC') from dual;
  CREATE TABLE "CJC"."CJC_T1" 
   ("A" NUMBER(*,0)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "CJCTBS"

set line 300
set pagesize 100
col table_name for a15
col tablespace_name for a15
select table_name,tablespace_name,pct_free,ini_trans,max_trans from user_tables where table_name='CJC_T1';
TABLE_NAME    TABLESPACE_NAME   PCT_FREE  INI_TRANS  MAX_TRANS
--------------- --------------- ---------- ---------- ----------
CJC_T1    CJCTBS    10    1        255

指定pctfree為0，同時指定initrans為1然後填滿相關資料塊，再對塊滿的資料進行更新模擬出itl的等待。

drop table cjc_t1 purge;
create table cjc_t1(a int) pctfree 0 initrans 1;
---alter table cjc_t1 pctfree 10;
---alter table cjc_t1 pctfree 0;
TABLE_NAME    TABLESPACE_NAME   PCT_FREE  INI_TRANS  MAX_TRANS
--------------- --------------- ---------- ---------- ----------
CJC_T1    CJCTBS    0    1        255

插入測試資料

begin
  for i in 1 .. 20000 loop
    insert into cjc_t1 values (i);
    commit;
  end loop;
end;

檢視測試資料對應的檔案號、塊號、資料量。

select file_id, block_id, count(*)
  from (select dbms_rowid.rowid_relative_fno(rowid) file_id,
               dbms_rowid.rowid_block_number(rowid) block_id
          from cjc_t1)
 group by file_id, block_id
 order by 3, 2;
   FILE_ID   BLOCK_IDCOUNT(*)
---------- ---------- ----------
 8   32     209
 8   11     733
 8   12     733
 8   13     733
 8   14     733
 8   15     733
 8   16     733
 8   17     733
 8   18     733
 8   19     733
 8   20     733
 8   21     733
 8   22     733
 8   23     733
 8   25     733
 8   26     733
 8   27     733
 8   28     733
 8   29     733
 8   30     733
 8   31     733
 8   33     733
 8   34     733
 8   35     733
 8   36     733
 8   37     733
 8   38     733
 8   39     733
28 rows selected.

插入20000條資料後可以發現該表有28個資料塊，填滿了除了32塊以外的其他資料塊。

接著匯出已經填滿的資料塊11. 

SQL> conn / as sysdba
Connected.
SQL> alter system dump datafile 8 block 11;
System altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle11/diag/rdbms/chendb/chendb/trace/chendb_ora_8418.trc

[oracle@cjcos01 trace]$ vim /u01/app/oracle11/diag/rdbms/chendb/chendb/trace/chendb_ora_8418.trc
......
Block header dump:  0x0200000b
 Object id on Block? Y
 seg/obj: 0x15ac6  csc: 0x00.2436e8  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x2000008 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0002.01c.00001141  0x00c051b5.0109.20  C---    0  scn 0x0000.002436e8
0x02   0x0007.01b.0000105b  0x00c0631e.0100.1a  C---    0  scn 0x0000.002436e7
bdba: 0x0200000b
data_block_dump,data header at 0x7fa1f9d42a64
===============
tsiz: 0x1f98
hsiz: 0x5cc
pbl: 0x7fa1f9d42a64
     76543210
flag=--------
ntab=1
nrow=733
frre=-1
fsbo=0x5cc
fseo=0xb94
avsp=0x7
tosp=0x7
0xe:pti[0]      nrow=733        offs=0
0x12:pri[0]     offs=0x1f91
0x14:pri[1]     offs=0x1f8a

接著匯出沒有填滿的資料塊32。

alter system dump datafile 8 block 32;
oradebug setmypid;
oradebug tracefile_name;

檢視trc資訊。

Block header dump:  0x02000020
 Object id on Block? Y
 seg/obj: 0x15ac6  csc: 0x00.247d61  itc: 2  flg: E  typ: 1 - DATA
     brn: 1  bdba: 0x2000018 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.011.00001174  0x00c06232.00bb.11  --U-    1  fsc 0x0000.00247d62
0x02   0x0009.007.000011a0  0x00c003d4.010f.09  C---    0  scn 0x0000.00247d61
bdba: 0x02000020
data_block_dump,data header at 0x7fa1f9d42a64
===============
tsiz: 0x1f98
hsiz: 0x1b4
pbl: 0x7fa1f9d42a64
     76543210
flag=--------
ntab=1
nrow=209
frre=-1
fsbo=0x1b4
fseo=0x1914
avsp=0x168b
tosp=0x168b
0xe:pti[0]      nrow=209        offs=0
0x12:pri[0]     offs=0x1f90

ITL說明：

1）Itl： ITL事務槽編號，ITL事務槽號的流水編號
2）Xid：代表對應的事務id（transac[X]tion identified），在回滾段事務表中有一條記錄和這個事務對應。
Xid由三列使用十六進位制編碼的數字列表示，
分別是：Undo Segment Number +Transaction Table Slot Number+ Wrap,
即由undo段號+undo槽號+undo槽號的覆蓋次數三部分組成，即usn.slot.sqn。
3）Uba：(Undo Block Address)，該事務對應的回滾段地址，記錄了最近一次的該記錄的前映象（修改前的值）。
Uba組成：Undo塊地址(undo檔案號和資料塊號)+回滾序列號+回滾記錄號。
多版本一致讀是Oracle保證讀操作不會被事務阻塞的重要特性。
當Server Process需要查詢一個正在被事務修改，但是尚未提交的資料時，就根據ITL上的uba定位到對應Undo前映象資料位置。
4）Flag：事務標誌位，即當前事務槽的狀態資訊。這個標誌位就記錄了這個事務的操作狀態，各個標誌的含義分別是：
----:事務是活動的，未提交，或者在塊清理前提交事務。
C---:事務已經提交，鎖已經被清除(提交)。
-B--:this undo record contains the undo for this ITL entry。
--U-:事務已經提交，但是鎖還沒有清除(快速提交)。
---T:
C-U-:
5）Lck：表示這個事務所影響的行數，鎖住了幾行資料，對應有幾個行鎖。我們看到01號事物槽Lck為3，因為該事物槽中的事物Flag為U，證明該事物已經提交，但是鎖還沒有清除。再比如對於下邊這個ITL：
我們看到01號事物槽Lck為0，因為該事物槽中的事物Flag為C，證明該事物已經提交，鎖也被清楚掉了，該事物槽可以被重用了。02號事物槽Lck為1，是因為我對第一行做了一個更新，並且沒有提交，Flag為“----”說明該事物是活動的。
6）Scn/Fsc：Commit SCN或者快速提交（Fast Commit Fsc）的SCN。 Scn=SCN of commited TX; Fsc=Free space credit(bytes)每條記錄中的行級鎖對應Itl條目lb，對應於Itl列表中的序號，即那個事務在該記錄上產生的鎖。一個事物只有在提交之後才會在ITL事物槽中記錄SCN。

透過bbed工具，檢視塊資訊：

準備檔案：

[oracle@cjcos01 bbed]$ pwd
/home/oracle/bbed
[oracle@cjcos01 bbed]$ ll -rth
total 20K
-rw-r--r-- 1 oracle oinstall 1.2K Dec 20 11:43 ssbbded.o
-rw-r--r-- 1 oracle oinstall 1.9K Dec 20 11:43 sbbdpt.o
-rw-r--r-- 1 oracle oinstall 8.5K Dec 20 11:43 bbedus.msb

將檔案複製到指定目錄

[oracle@cjcos01 bbed]$ cp bbedus.msb $ORACLE_HOME/rdbms/mesg/
[oracle@cjcos01 bbed]$ cp ssbbded.o $ORACLE_HOME/rdbms/lib/
[oracle@cjcos01 bbed]$ cp sbbdpt.o $ORACLE_HOME/rdbms/lib/

編譯

[oracle@cjcos01 bbed]$ make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
Linking BBED utility (bbed)
rm -f /u01/app/oracle11/product/11.2.0/dbhome_1/bin/bbed
gcc -o /u01/app/oracle11/product/11.2.0/dbhome_1/bin/bbed -m64 -z noexecstack -L/u01/app/oracle11/product/11.2.0/dbhome_1/rdbms/lib/ -L/u01/app/oracle11/product/11.2.0/dbhome_1/lib/ -L/u01/app/oracle11/product/11.2.0/dbhome_1/lib/stubs/  /u01/app/oracle11/product/11.2.0/dbhome_1/lib/s0main.o /u01/app/oracle11/product/11.2.0/dbhome_1/rdbms/lib/ssbbded.o /u01/app/oracle11/product/11.2.0/dbhome_1/rdbms/lib/sbbdpt.o `cat /u01/app/oracle11/product/11.2.0/dbhome_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -ldbtools11 -lclntsh  `cat /u01/app/oracle11/product/11.2.0/dbhome_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle11/product/11.2.0/dbhome_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lmm -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `cat /u01/app/oracle11/product/11.2.0/dbhome_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle11/product/11.2.0/dbhome_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11   `cat /u01/app/oracle11/product/11.2.0/dbhome_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle11/product/11.2.0/dbhome_1/lib -lm    `cat /u01/app/oracle11/product/11.2.0/dbhome_1/lib/sysliblist` -ldl -lm   -L/u01/app/oracle11/product/11.2.0/dbhome_1/lib

bbed 預設密碼" blockedit"

[oracle@cjcos01 bbed]$ bbed
Password: 
BBED: Release 2.0.0.0.0 - Limited Production on Sun Dec 20 11:51:23 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED>

使用BBED 工具之前需要建立filelist 檔案

set linesize 100
col name for a50
set feedback off;
set heading off;
spool /home/oracle/filelist.txt
select file#,name,bytes from v$datafile order by 1;
 1 /u01/app/oracle11/oradata/chendb/system01.dbf       786432000
 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf       587202560
 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf      209715200
 4 /u01/app/oracle11/oradata/chendb/users01.dbf  5242880
 5 /u01/app/oracle11/oradata/chendb/example01.dbf      328335360
 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf 1048576
 7 /u01/app/oracle11/oradata/chendb/cjctbs02.dbf 1048576
 8 /u01/app/oracle11/oradata/chendb/cjctbs03.dbf 1048576
 9 /u01/app/oracle11/oradata/chendb/cjctbs05.dbf 1048576
spool off

建立bbed.par檔案

[oracle@cjcos01 ~]$ touch bbed.par
[oracle@cjcos01 ~]$ vim bbed.par
blocksize=8192
listfile=/home/oracle/filelist.txt
mode=edit

登入

[oracle@cjcos01 ~]$ bbed parfile=bbed.par
Password: 
BBED: Release 2.0.0.0.0 - Limited Production on Sun Dec 20 11:56:31 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> help all
BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /u01/app/oracle11/oradata/chendb/system01.dbf                    96000
     2  /u01/app/oracle11/oradata/chendb/sysaux01.dbf                    71680
     3  /u01/app/oracle11/oradata/chendb/undotbs01.dbf                   25600
     4  /u01/app/oracle11/oradata/chendb/users01.dbf                       640
     5  /u01/app/oracle11/oradata/chendb/example01.dbf                   40080
     6  /u01/app/oracle11/oradata/chendb/cjctbs01.dbf                      128
     7  /u01/app/oracle11/oradata/chendb/cjctbs02.dbf                      128
     8  /u01/app/oracle11/oradata/chendb/cjctbs03.dbf                      128
     9  /u01/app/oracle11/oradata/chendb/cjctbs05.dbf                      128

指定檢視file 8 block 11資訊

BBED> set file 8 block 11
FILE#          8
BLOCK#         11

檢視資訊

BBED> map

 File: /u01/app/oracle11/oradata/chendb/cjctbs03.dbf (8)
 Block: 11                                    Dba:0x0200000b
------------------------------------------------------------
 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[733]                              @118     
 ub1 freespace[1480]                        @1584    ---該塊空閒1480byte
 ub1 rowdata[5124]                          @3064    
 ub4 tailchk                                @8188

BBED>  p ktbbh
struct ktbbh, 72 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x00015ac6
      ub4 ktbbhod1                          @24       0x00015ac6
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0x00249012
      ub2 kscnwrp                           @32       0x0000
   sb2 ktbbhict                             @36       2
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x02000008
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0002
         ub2 kxidslt                        @46       0x001c
         ub4 kxidsqn                        @48       0x00001141
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00c051b5
         ub2 kubaseq                        @56       0x0109
         ub1 kubarec                        @58       0x20
      ub2 ktbitflg                          @60       0x8000 (KTBFCOM)
      union _ktbitun, 2 bytes               @62      
         sb2 _ktbitfsc                      @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x002436e8
   struct ktbbhitl[1], 24 bytes             @68      
      struct ktbitxid, 8 bytes              @68      
         ub2 kxidusn                        @68       0x000a
         ub2 kxidslt                        @70       0x001b
         ub4 kxidsqn                        @72       0x00001097
      struct ktbituba, 8 bytes              @76      
         ub4 kubadba                        @76       0x00c05ffa
         ub2 kubaseq                        @80       0x00c5
         ub1 kubarec                        @82       0x33
      ub2 ktbitflg                          @84       0x8000 (KTBFCOM)
      union _ktbitun, 2 bytes               @86      
         sb2 _ktbitfsc                      @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x00249010

指定檢視file 8 block 32資訊

BBED> set file 8 block 32
FILE#          8
BLOCK#         32

檢視資訊

BBED> map
 File: /u01/app/oracle11/oradata/chendb/cjctbs03.dbf (8)
 Block: 32                                    Dba:0x02000020
------------------------------------------------------------
 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[209]                              @118     
 ub1 freespace[5984]                        @536     
 ub1 rowdata[1668]                          @6520    
 ub4 tailchk                                @8188

更新資料，檢視行遷移情況

SQL> update cjc_t1 set a=10000000000000000000000000000000000000000000000000000000000000  where a=1500;
1 row updated.
SQL> commit;
Commit complete.

BBED> set file 8 block 11
FILE#          8
BLOCK#         11

檢視資訊 

BBED> map
 File: /u01/app/oracle11/oradata/chendb/cjctbs03.dbf (8)
 Block: 11                                    Dba:0x0200000b
------------------------------------------------------------
 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[733]                              @118     
 ub1 freespace[1480]                        @1584    
 ub1 rowdata[5124]                          @3064    
 ub4 tailchk                                @8188

檢視行遷移情況

SQL> @?/rdbms/admin/utlchain.sql
Table created.
SQL> desc chained_rows
 Name   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER_NAME    VARCHAR2(30)
 TABLE_NAME    VARCHAR2(30)
 CLUSTER_NAME    VARCHAR2(30)
 PARTITION_NAME     VARCHAR2(30)
 SUBPARTITION_NAME    VARCHAR2(30)
 HEAD_ROWID    ROWID
 ANALYZE_TIMESTAMP    DATE
SQL> conn cjc/cjc
Connected.
SQL> set autotrace traceonly stat
SQL> set linesize 1000
SQL> select * from cjc_t1 where a>20000000000000000;
Statistics
----------------------------------------------------------
  5  recursive calls
  0  db block gets
 64  consistent gets
  0  physical reads
  0  redo size
519  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed
  
update cjc_t1 set a=to_number(LPAD('1', 22, '1'))  where a=1521;
commit;
SQL> select * from cjc_t1 where a>20000000000000000;
Statistics
----------------------------------------------------------
  0  recursive calls
  0  db block gets
 32  consistent gets
  0  physical reads
  0  redo size
583  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  2  rows processed
 
update cjc_t1 set a=to_number(LPAD('1', 10, '1'))  where a=1525;
commit;
update cjc_t1 set a=to_number(LPAD('1', 10, '1'))  where a=1526;
commit;
update cjc_t1 set a=1527  where a=1527;
SQL> analyze table cjc.cjc_t1 list chained rows into sys.chained_rows;
Table analyzed.

模擬enq: TX - allocate ITL entry等待事件

會話1

SQL> conn cjc/cjc
Connected.
SQL> select * from v$mystat where rownum<=1;
       SID STATISTIC#   VALUE
---------- ---------- ----------
 1    0       0
SQL> update cjc_t1 set a=1500  where a=1500;
1 row updated.

會話56

SQL> conn cjc/cjc
Connected.
SQL> select * from v$mystat where rownum<=1;
       SID STATISTIC#   VALUE
---------- ---------- ----------
56    0       0
SQL> update cjc_t1 set a=1501  where a=1501;
1 row updated.

會話35

SQL> conn cjc/cjc
Connected.
SQL> select * from v$mystat where rownum<=1;
       SID STATISTIC#   VALUE
---------- ---------- ----------
35    0       0
SQL> update cjc_t1 set a=1502  where a=1502;

...卡住

檢視會話資訊，35號會話出現enq: TX - allocate ITL entry等待事件。

select sid,
       serial#,
       username,
       status,
       program,
       sql_id,
       event,
       p1,
       p2,
       p2text,
       p3
  from v$session
 where username = 'CJC'
   and machine = 'cjcos01';
   SIDSERIAL#USERNAMESTATUSPROGRAMSQL_IDEVENTP1P2P2TEXTP3
1113CJCINACTIVEsqlplus@cjcos01 (TNS V1-V3)SQL*Net message from client16508152321#bytes0
23559CJCACTIVEsqlplus@cjcos01 (TNS V1-V3)05u6gsqjgrvxqenq: TX - allocate ITL entry1415053316524304usn<<16 | slot4480
356253CJCINACTIVEsqlplus@cjcos01 (TNS V1-V3)SQL*Net message from client16508152321#bytes0

檢視enq: TX - allocate ITL entry等待事件描述

select * from v$event_name where name like 'enq: TX - allocate ITL entry';
   EVENT#EVENT_IDNAMEPARAMETER1PARAMETER2PARAMETER3WAIT_CLASS_IDWAIT_CLASS#WAIT_CLASS
1242281768874enq: TX - allocate ITL entryname|modeusn<<16 | slotsequence32902558402Configuration

再次檢視資料塊資訊

SQL> conn / as sysdba
Connected.
SQL> alter system dump datafile 8 block 11;
System altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle11/diag/rdbms/chendb/chendb/trace/chendb_ora_14553.trc

檢視trace資訊

[root@cjcos01 ~]# vim /u01/app/oracle11/diag/rdbms/chendb/chendb/trace/chendb_ora_14553.trc
......
Block header dump:  0x0200000b
 Object id on Block? Y
 seg/obj: 0x15ac6  csc: 0x00.2436e8  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x2000008 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0007.018.00001093  0x00c06349.0100.32  ----    1  fsc 0x0000.00000000
0x02   0x0008.010.00001180  0x00c002a2.0136.30  ----    1  fsc 0x0000.00000000
bdba: 0x0200000b
data_block_dump,data header at 0x7f3d1e7bda64
===============
tsiz: 0x1f98
hsiz: 0x5cc
pbl: 0x7f3d1e7bda64
     76543210
flag=--------
ntab=1
nrow=733
frre=-1
fsbo=0x5cc
fseo=0xb94
avsp=0x7
tosp=0x7

update cjc_t1 set a=1505  where a=1505;
update cjc_t1 set a=1506  where a=1506;
select sid,
       serial#,
       username,
       status,
       program,
       sql_id,
       event,
       p1,
       p2,
       p2text,
       p3
  from v$session
 where username = 'CJC'
   and machine = 'cjcos01';
    SIDSERIAL#USERNAMESTATUSPROGRAMSQL_IDEVENTP1P2P2TEXTP3
1115CJCINACTIVEsqlplus@cjcos01 (TNS V1-V3)SQL*Net message from client16508152321#bytes0
23563CJCACTIVEsqlplus@cjcos01 (TNS V1-V3)05u6gsqjgrvxqenq: TX - allocate ITL entry1415053316655387usn<<16 | slot4247
35212975CJCACTIVEsqlplus@cjcos01 (TNS V1-V3)g9tmbcuv84aq6enq: TX - allocate ITL entry1415053316655387usn<<16 | slot4247
456253CJCINACTIVEsqlplus@cjcos01 (TNS V1-V3)SQL*Net message from client16508152321#bytes0
55831CJCACTIVEsqlplus@cjcos01 (TNS V1-V3)3pu5608w2zktcenq: TX - allocate ITL entry1415053316458776usn<<16 | slot4243

如何解決enq: TX - allocate ITL entry等待事件問題：

業務需要需要考慮：

1 業務需要考慮對應SQL是否可以最佳化，比如最佳化並行執行次數、減少訪問頻率等。
2 考慮是否可以修改業務邏輯,減少頻繁訪問等。
3 考慮多個SQL在一個事務內完成，減少itl分配次數，減少併發執行多個事務。

資料庫層需要考慮：

Troubleshooting waits for 'enq: TX - allocate ITL entry' (Doc ID 1472175.1)

為了減少enq:TX-allocate ITL entry“等待事件，我們需要遵循以下步驟：

一：增加INITRANS

1) 根據表中的事務數，可以需要更改INITRANS的值。例如這裡改為50：

alter table <table name> INITRANS 50;

2) 使用下面命令重新組織表。

alter table <table_name> move;

3) rebuild這張表下的所有索引。

alter index <index_name> rebuild INITRANS 50;

二：增加PCTFREE

如果透過增加INITRANS無法解決問題，請嘗試增加PCTFREE。增加PCTFREE可以保留更多的空間，因此可以將相同數量的行分佈到更多的塊上。這意味著，還有更多的ITL插槽可用。

1 將行分散到更多的塊中也有助於減少這種等待事件。

alter table <table name>  PCTFREE 20;

2) 使用下面命令重新組織表。

alter table <table_name> move;

3) Rebuild index

alter index index_name rebuild PCTFREE 20;

三：同時增加INITRANS和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;

注意：

可以為表/索引修改INITRANS的新值。但更改後的值僅對新塊生效。需要重新生成物件，以便再次初始化塊。
對於索引，這意味著需要重新生成或重新建立索引。
對於表，可透過以下方式實現：
exp/imp
alter table move
dbms_redefenition


2020-12-20 19:52 chenjuchao

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2743807/，如需轉載，請註明出處，否則將追究法律責任。

相關文章