[20150721]enq TX - allocate ITL entry.txt
--昨天我做了一个测试链接：
http://blog.itpub.net/267265/viewspace-1742243/
--本想通过这个例子说明为什么8K数据块Hakan Factor=736?
--晚上我想到一种这种特殊的表会不会产生enq TX - allocate ITL entry，也就是itl不足的情况。
1.建立测试环境：
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SCOTT@test> drop table test purge ;
Table dropped.
SCOTT@test> create table test (id varchar2(100)) pctfree 0 tablespace mssm ;
Table created.
SCOTT@test> insert into test select null from dba_objects where rownum<=1000;
1000 rows created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select rowid,test.* from test where rownum<=1;
ROWID ID
------------------ --------------------
AAAQazAAGAAAAAKAAA
SCOTT@test> alter system checkpoint;
System altered.
SCOTT@test> @ &r/lookup_rowid AAAQazAAGAAAAAKAAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
67251 6 10 0 6,10 alter system dump datafile 6 block 10 ;
2.通过bbed观察：
BBED> set dba 6,10
DBA 0x0180000a (25165834 6,10)
BBED> map
File: /mnt/ramdisk/test/mssm01.dbf (6)
Block: 10 Dba:0x0180000a
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @92
struct kdbt[1], 4 bytes @106
sb2 kdbr[734] @110
ub1 freespace[4381] @1578
ub1 rowdata[2229] @5959
ub4 tailchk @8188
--可以发现freespace=4381, sb2 kdbr[734] 也说明插入了734条记录。还有大量的空间。
3.昨天我的测试如果即使做这样的修改也会出现行迁移的情况：
--但是如果我打开3个会话，修改同一块里面不同记录会出现什么情况呢？
--session 1:
SCOTT@test> @ &r/spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
139 953 19894 alter system kill session '139,953' immediate;
SCOTT@test> update test set id='12345' where rowid='AAAQa0AAGAAAAAKAAA' ;
1 row updated.
--不提交。
--session 2:
SCOTT@test> @ &r/spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
147 1493 19921 alter system kill session '147,1493' immediate;
SCOTT@test> update test set id='12345' where rowid='AAAQa0AAGAAAAAKAAB' ;
1 row updated.
--不提交。
--session 3:
SCOTT@test> @ &r/spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
137 61 19933 alter system kill session '137,61' immediate;
SCOTT@test> update test set id='12345' where rowid='AAAQa0AAGAAAAAKAAC' ;
--挂起。
4.通过bbed观察：
SCOTT@test> alter system checkpoint;
System altered.
SCOTT@test> select * from V$SESSION_EVENT where sid in (137,139,147);
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---- ------------------------------ ------------ -------------- ------------ ------------ ------------ ----------------- ------------ ------------- ------------ --------------------
137 enq: TX - allocate ITL entry 37 36 10382 280.58 293 103815779 281768874 3290255840 2 Configuration
137 SQL*Net message to client 15 0 0 0 0 9 2067390145 2000153315 7 Network
137 SQL*Net message from client 15 0 1976 131.71 1291 19755790 1421975091 2723168908 6 Idle
137 SQL*Net break/reset to client 1 0 0 .02 0 168 1963888671 4217450380 1 Application
139 SQL*Net message to client 18 0 0 0 0 15 2067390145 2000153315 7 Network
139 SQL*Net message from client 17 0 13099 770.52 10422 130988660 1421975091 2723168908 6 Idle
139 SQL*Net break/reset to client 2 0 0 .01 0 191 1963888671 4217450380 1 Application
147 SQL*Net message to client 15 0 0 0 0 10 2067390145 2000153315 7 Network
147 SQL*Net message from client 14 0 1381 98.67 940 13813684 1421975091 2723168908 6 Idle
9 rows selected.
--可以发现出现"enq: TX - allocate ITL entry" 等待事件。
BBED> set dba 6,10
DBA 0x0180000a (25165834 6,10)
BBED> map
File: /mnt/ramdisk/test/mssm01.dbf (6)
Block: 10 Dba:0x0180000a
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @92
struct kdbt[1], 4 bytes @106
sb2 kdbr[734] @110
ub1 freespace[4390] @1578
ub1 rowdata[2220] @5968
ub4 tailchk @8188
--freespace=4390!!竟然出现itl槽不足的情况。
BBED> p *kdbr[0]
rowdata[9]
----------
ub1 rowdata[9] @5977 0x20
BBED> x /rc
rowdata[9] @5977
----------
flag@5977: 0x20 (KDRHFH)
lock@5978: 0x02
cols@5979: 0
nrid@5980:0x0180000b.10a
BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0] @5968 0x20
BBED> x /rc
rowdata[0] @5968
----------
flag@5968: 0x20 (KDRHFH)
lock@5969: 0x01
cols@5970: 0
nrid@5971:0x0180000b.10b
BBED> p *kdbr[2]
rowdata[1461]
-------------
ub1 rowdata[1461] @7429 0x2c
BBED> x /rc
rowdata[1461] @7429
-------------
flag@7429: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7430: 0x00
cols@7431: 0
--可以发现前面2条记录出现了行迁移。从另外的方面证明我的猜测，oracle必须为可能出现的行迁移保留必要的自由空间，即使在这种
--情况有许多自由空间，oracle依旧报itl槽不足。当然这种情况在实际中非常难出现。两种可能合在一起再做update也许会出现：
1.每条记录很短。
2.pctfree设置很小=0.
--另外说明一个很有兴趣的测试，如果你等上1段时间，比如2，3分钟，然后在会话2提交，各位继续观察，可以发现会话3还是依旧无法提交。
--出现的依旧是enq: TX - allocate ITL entry等待。视乎是会话3仅仅在等会话1提交，如果你会话1提交，会话3才会解除挂起状态。
--有兴趣看链接：http://blog.itpub.net/267265/viewspace-731657/，当然这个问题出现10.2.0.4。