当数据块没有足够的空间来分配ITL事务槽的时候，那么事务将会处于enq: TX - allocate ITL entry等待中。
本文进行一个简单的模拟。
SQL> create table t(x int,y varchar2(200)) pctfree 0 initrans 1;
Table created.
SQL> insert into t select rownum,object_name from all_objects where rownum<=1000;
1000 rows created.
SQL> commit;
Commit complete.
SQL> desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER(38)
Y VARCHAR2(200)
SQL> select distinct dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
5 681445
5 681448
5 681446
5 681444
选取BLOCK 681444作为测试块。
SQL> alter system dump datafile 5 block 681444;
System altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/oracle/admin/testb/udump/testb2_ora_464334.trc
Block header dump: 0x014a65e4
Object id on Block? Y
seg/obj: 0xa7f65c csc: 0x83e.7d87ebd2 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x14a65e1 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0011.009.0004de5b 0xc9c0047f.2ca1.13 --U- 305 fsc 0x0000.7d87ebd3
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x110444064
===============
tsiz: 0x1f98
hsiz: 0x274
pbl: 0x110444064
bdba: 0x014a65e4
76543210
flag=--------
ntab=1
nrow=305
frre=-1
fsbo=0x274
fseo=0x284
avsp=0x10
tosp=0x10
从上面可以看出上面的BLOCK 681444中只有2个ITL，而且剩余的自由空间为fseo-fsbo==0x284-0x274=0x10
而分配一个ITL需要24BYTE，因此这个数据块最大只能支持2个事务。
SQL> select x
2 from (select x, y, dbms_rowid.rowid_block_number(rowid) block# from t)
3 where block# = 681444 and rownum<=4;
X
----------
319
320
321
322
通过上面查询可以知道记录319,320,321都在块681444中。
SESSION 1:
SQL> update t set y=y where x=319;
1 row updated.
SESSION 2：
SQL> update t set y=y where x=320;
1 row updated.
SESSION 3：
SQL> update scott.t set y=y where x=321;
将会HANG住。
通过下面的查询可以看到有1个会话处于ITL等待中：
SQL> select event from v$session where event like 'enq%';
EVENT
--------------------------------------------------------------------------------
enq: TX - allocate ITL entry
SESSION 4：
SQL> update t set y=y where x=321;
将会HANG住。
在此运行select event from v$session where event like 'enq%' 将会看到2个会话处于enq: TX - allocate ITL entry等待中。
SQL> select event from v$session where event like 'enq%';
EVENT
--------------------------------------------------------------------------------
enq: TX - allocate ITL entry
SQL> /
EVENT
--------------------------------------------------------------------------------
enq: TX - allocate ITL entry
enq: TX - allocate ITL entry
阅读(917) | 评论(0) | 转发(0) |
