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

IT小Chen 2021-04-13

1319

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争用。

创建表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_ID  COUNT(*)
---------- ---------- ----------
   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';
     SID  SERIAL#  USERNAME  STATUS  PROGRAM  SQL_ID  EVENT  P1  P2  P2TEXT  P3
1  1  13  CJC  INACTIVE  sqlplus@cjcos01 (TNS V1-V3)    SQL*Net message from client  1650815232  1  #bytes  0
2  35  59  CJC  ACTIVE  sqlplus@cjcos01 (TNS V1-V3)  05u6gsqjgrvxq  enq: TX - allocate ITL entry  1415053316  524304  usn<<16 | slot  4480
3  56  253  CJC  INACTIVE  sqlplus@cjcos01 (TNS V1-V3)    SQL*Net message from client  1650815232  1  #bytes  0

查看enq: TX - allocate ITL entry等待事件描述

select * from v$event_name where name like 'enq: TX - allocate ITL entry';
     EVENT#  EVENT_ID  NAME  PARAMETER1  PARAMETER2  PARAMETER3  WAIT_CLASS_ID  WAIT_CLASS#  WAIT_CLASS
1  242  281768874  enq: TX - allocate ITL entry  name|mode  usn<<16 | slot  sequence  3290255840  2  Configuration

再次查看数据块信息

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';
      SID  SERIAL#  USERNAME  STATUS  PROGRAM  SQL_ID  EVENT  P1  P2  P2TEXT  P3
1  1  15  CJC  INACTIVE  sqlplus@cjcos01 (TNS V1-V3)    SQL*Net message from client  1650815232  1  #bytes  0
2  35  63  CJC  ACTIVE  sqlplus@cjcos01 (TNS V1-V3)  05u6gsqjgrvxq  enq: TX - allocate ITL entry  1415053316  655387  usn<<16 | slot  4247
3  52  12975  CJC  ACTIVE  sqlplus@cjcos01 (TNS V1-V3)  g9tmbcuv84aq6  enq: TX - allocate ITL entry  1415053316  655387  usn<<16 | slot  4247
4  56  253  CJC  INACTIVE  sqlplus@cjcos01 (TNS V1-V3)    SQL*Net message from client  1650815232  1  #bytes  0
5  58  31  CJC  ACTIVE  sqlplus@cjcos01 (TNS V1-V3)  3pu5608w2zktc  enq: TX - allocate ITL entry  1415053316  458776  usn<<16 | slot  4243

如何解决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:35 chenjuchao

数据库

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

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

评论