[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。

[20150721]enq TX - allocate ITL entry

相關文章