oracle job enq tx,【学习笔记】Oracle等待事件 enq:TX–allocate ITL entry产生原因和解决办法...

天萃荷净

运维DBA反映Oracle数据库出现enq:TX–allocate ITL entry等待事件，结合案例分析该等待事件产生原因和解决办法

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

1.创建测试对象

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

Table created.

SQL> insert into t_oracleplus 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_oracleplus;

FILE# BLOCK#

---------- ----------

4 32

2.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_oracleplus;

TRIM(NAME)

--------------------------------------------------------------------------------

ICOL$

I_USER1

CON$

UNDO$

SQL> update t_oracleplus set name='WWW.oracleplus.COM' WHERE name='ICOL$';

1 row updated.

--session 2

SQL> update t_oracleplus set name='www.orasos.com' where name='UNDO$';

1 row updated.

--session 3

SQL> update t_oracleplus set name='www.oracleplus.com' where name='CON$';

1 row updated.

--session 4

SQL> update t_oracleplus set name='www.oracleplus.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_oracleplus set name='www.oracleplus.com' where name='I_USER1';

1 row updated.

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

4.总结说明

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

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle等待事件 enq:TX–allocate ITL entry产生原因和解决办法