分类目录归档：Oracle性能优化

今天在分析一份awr中发现了较为明显的enq: TX – allocate ITL entry等待,这里通过试验详细重现了enq: TX – allocate ITL entry等待
创建测试对象

SQL> create table t_xifenfei (name char(2000)) pctfree 0 initrans 1;

Table created.

SQL> insert into t_xifenfei select object_name from all_objects    where rownum < 5;

4 rows created.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

SQL>  select distinct dbms_rowid.rowid_relative_fno(rowid) file#,
  2  dbms_rowid.rowid_block_number(rowid) block# from t_xifenfei;

     FILE#     BLOCK#
---------- ----------
         4         32

bbed查看block

BBED> set block 32
        BLOCK#          32

BBED> map
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 32                                    Dba:0x00000000
------------------------------------------------------------
 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[4]                                @118     

 ub1 freespace[38]                          @126    --该block空闲空间为38byte 

 ub1 rowdata[8024]                          @164     

 ub4 tailchk                                @8188    


BBED> p ktbbh
struct ktbbh, 72 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x0000d318
      ub4 ktbbhod1                          @24       0x0000d318
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0xc0320e3b
      ub2 kscnwrp                           @32       0x0b2c
   b2 ktbbhict                              @36       2
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x01000019
   struct ktbbhitl[0], 24 bytes             @44       --1个itl slot为24byte
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0015
         ub2 kxidslt                        @46       0x0019
         ub4 kxidsqn                        @48       0x00000005
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x0080009d
         ub2 kubaseq                        @56       0x0002
         ub1 kubarec                        @58       0x28
      ub2 ktbitflg                          @60       0x2004 (KTBFUPB)
      union _ktbitun, 2 bytes               @62      
         b2 _ktbitfsc                       @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0xc0320e4e
   struct ktbbhitl[1], 24 bytes             @68       --有两个itl slot
      struct ktbitxid, 8 bytes              @68      
         ub2 kxidusn                        @68       0x0000
         ub2 kxidslt                        @70       0x0000
         ub4 kxidsqn                        @72       0x00000000
      struct ktbituba, 8 bytes              @76      
         ub4 kubadba                        @76       0x00000000
         ub2 kubaseq                        @80       0x0000
         ub1 kubarec                        @82       0x00
      ub2 ktbitflg                          @84       0x0000 (NONE)
      union _ktbitun, 2 bytes               @86      
         b2 _ktbitfsc                       @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x00000000

通过bbed我们可以得出如下结论：
1.该block剩余38 byte 空闲空间可以用来存放数据
2.该block 初始化 itl 为2(和我们设置的1不相符)
3.一个itl slot为24byte

更新表记录

--session 1
SQL> select trim(name) from t_xifenfei;

TRIM(NAME)
--------------------------------------------------------------------------------
ICOL$
I_USER1
CON$
UNDO$

SQL> update t_xifenfei set name='WWW.XIFENFEI.COM' WHERE name='ICOL$';

1 row updated.

--session 2
SQL> update t_xifenfei set name='www.orasos.com' where name='UNDO$';

1 row updated.

--session 3
SQL> update t_xifenfei set name='www.xifenfei.com' where name='CON$';

1 row updated.

--session 4
SQL> update t_xifenfei set name='www.xifenfei.com' where name='I_USER1';
--hang住

--session 5
SQL> select event from v$session where  event like 'enq%';

EVENT
----------------------------------------------------------------
enq: TX - allocate ITL entry

通过这里可以看到我们模拟了4个update 该block操作(均未提交)，前面三个可以正常的update操作，第四个出现了enq: TX – allocate ITL entry等待,根据我们知识分析(未提交事务的itl不能覆盖,一个dml操作需要一个itl),这里使用了3个itl slot,而我们已经知道一个itl 需要24byte，该block初始化有2个itl,现在这里有3个dml操作成功,即占用了3个itl,所以该block的剩余空间只有38-24=14 byte<24byte,因此无法分配第四个itl slot从而出现了enq: TX - allocate ITL entry等待
bbed验证上述分析

BBED> map
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 32                                    Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 96 bytes                     @20      

 struct kdbh, 14 bytes                      @124     

 struct kdbt[1], 4 bytes                    @138     

 sb2 kdbr[4]                                @142     

 ub1 freespace[14]                          @150     

 ub1 rowdata[8024]                          @164     

 ub4 tailchk                                @8188    


BBED> p ktbbh
struct ktbbh, 96 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x0000d318
      ub4 ktbbhod1                          @24       0x0000d318
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0xc0320eb0
      ub2 kscnwrp                           @32       0x0b2c
   b2 ktbbhict                              @36       3
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x01000019
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0003
         ub2 kxidslt                        @46       0x001f
         ub4 kxidsqn                        @48       0x00000208
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00800027
         ub2 kubaseq                        @56       0x0414
         ub1 kubarec                        @58       0x01
      ub2 ktbitflg                          @60       0x0001 (NONE)
      union _ktbitun, 2 bytes               @62      
         b2 _ktbitfsc                       @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x00000000
   struct ktbbhitl[1], 24 bytes             @68      
      struct ktbitxid, 8 bytes              @68      
         ub2 kxidusn                        @68       0x000a
         ub2 kxidslt                        @70       0x000f
         ub4 kxidsqn                        @72       0x00000185
      struct ktbituba, 8 bytes              @76      
         ub4 kubadba                        @76       0x0080008a
         ub2 kubaseq                        @80       0x01a6
         ub1 kubarec                        @82       0x0c
      ub2 ktbitflg                          @84       0x0001 (NONE)
      union _ktbitun, 2 bytes               @86      
         b2 _ktbitfsc                       @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x00000000
   struct ktbbhitl[2], 24 bytes             @92      
      struct ktbitxid, 8 bytes              @92      
         ub2 kxidusn                        @92       0x0008
         ub2 kxidslt                        @94       0x002a
         ub4 kxidsqn                        @96       0x00000217
      struct ktbituba, 8 bytes              @100     
         ub4 kubadba                        @100      0x008000cc
         ub2 kubaseq                        @104      0x0291
         ub1 kubarec                        @106      0x12
      ub2 ktbitflg                          @108      0x0001 (NONE)
      union _ktbitun, 2 bytes               @110     
         b2 _ktbitfsc                       @110      0
         ub2 _ktbitwrp                      @110      0x0000
      ub4 ktbitbas                          @112      0x00000000

可以看到剩余空间为14byte，事务槽为3个，因此上述分析为正确。

提交会话测试

--session 1
SQL> commit;

Commit complete.

--session 4
SQL> update t_xifenfei set name='www.xifenfei.com' where name='I_USER1';


1 row updated.

证明commit掉事务后,itl slot可以重利用

总结说明
enq: TX – allocate ITL entry为分配ITL条目的等待，因为PCTFREE不足，BLOCK中没有足够空间分配ITL，ORACLE只能重用ITL，但是这个时候由于没有COMMIT，无法重用ITL，所以会出现allocate ITL等待事件。要解决此类问题，我们可以考虑增加PCTFREE和initrans大小,需要注意该修改只能对于新block生效,已经存放数据的block不会发生改变.另外可以考虑修改业务逻辑,减少频繁访问