[20150720]為什麼8K資料塊Hakan Factor=736.txt

--前幾天被別人問及這個問題，還真不好回答。
--仔細思考，我覺得與行遷移有關，行遷移發生時，rowid不會變化。資料資訊被移動另外的塊，在塊內保留一個指標。
--也就是講最小這條記錄僅僅包含一個rowid指標。
--還是透過例子來說明情況：

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> 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
------------------ -------
AAAQZYAAGAAAAAKAAA

SCOTT@test> @&r/lookup_rowid AAAQZYAAGAAAAAKAAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
67160 6 10 0 6,10 alter system dump datafile 6 block 10 ;

SCOTT@test> select count(*) from test where rowid between 'AAAQZYAAGAAAAAKAAA' and 'AAAQZYAAGAAAAAKDDD';
COUNT(*)
------------
734

SCOTT@test> select spare1 from sys.tab$ where obj#=67160;
SPARE1
------------
736

-- 1塊僅僅734 條記錄，比規定最大少3條記錄。(注意spare1=736,也就是最大行號736,實際上就是每塊最大插入737條記錄(行號從0開始).)

SCOTT@test> alter system checkpoint;
System altered.

2.透過bbed觀察：
BBED> set dba 6,10
DBA 0x0180000a (25165834 6,10)

BBED> p *kdbr[0]
rowdata[1437]
-------------
ub1 rowdata[1437] @7423 0x2c

BBED> x /rcccc
rowdata[1437] @7423
-------------
flag@7423: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7424: 0x01
cols@7425: 0

BBED> x /rc
rowdata[1437] @7423
-------------
flag@7423: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7424: 0x01
cols@7425: 0

--可以發現NULL並沒有記錄，一條記錄僅僅佔用3個位元組。 2c 01 00

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[4408]                        @1578
ub1 rowdata[2202]                          @5986
ub4 tailchk                                @8188

-- freespace = 4408 , 幾乎一半的空間剩下。2202/3=734,說明資訊對上。

BBED> p kdbr[733]
sb2 kdbr[733] @1576 6563

--說明塊頭部包括行目錄，已經佔用1578位元組。

--這樣留給資料部分是：

8192-1578-4=6610 (扣除尾部tailchk4個位元組)
6610/734=9.0054495912806539595
6610-734*9=4

--這樣計算也就是每條記錄最多9個位元組。每條記錄前面已經佔用3個位元組，這樣僅僅剩下6個位元組。(僅僅夠保留rowid)
--做一個大膽的猜測，如果734條記錄全部發生行遷移，rowid指標必須保留，這樣正好夠。

--換一句話講假設修改如下：
update test set id='1' where rowid='AAAQZYAAGAAAAAKAAA';

--僅僅1條記錄，在這樣的情況下(注意記錄前面有1個位元組儲存字串長度)就不會發生行遷移,但是如果修改如下：
update test set id='123456' where rowid='AAAQZYAAGAAAAAKAAA';

--這樣有會發生行遷移，繼續測試看看：

3.繼續測試：
SCOTT@test> update test set id='1' where rowid='AAAQZYAAGAAAAAKAAA';
1 row updated.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> alter system checkpoint;
System altered.

BBED> set dba 6,10
DBA 0x0180000a (25165834 6,10)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @5981 0x2c

BBED> x /rc
rowdata[0] @5981
----------
flag@5981: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5982: 0x02
cols@5983: 1

col 0[1] @5984: 1

--沒有發生行遷移。
SCOTT@test> update test set id='12' where rowid='AAAQZYAAGAAAAAKAAA';
1 row updated.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> alter system checkpoint;
System altered.

BBED> x /rc
rowdata[0] @5975
----------
flag@5975: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5976: 0x01
cols@5977: 1

col 0[2] @5978: 12

--沒有發生行遷移。
SCOTT@test> update test set id='123' where rowid='AAAQZYAAGAAAAAKAAA';
1 row updated.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> alter system checkpoint;
System altered.

BBED> x /rc
rowdata[0] @5968
----------
flag@5968: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5969: 0x02
cols@5970: 1

col 0[3] @5971: 123

SCOTT@test> update test set id='1234' where rowid='AAAQZYAAGAAAAAKAAA';
1 row updated.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> alter system checkpoint;
System altered.

BBED> x /rc
rowdata[0]                                  @5960
----------
flag@5960: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5961: 0x01
cols@5962:    1
col    0[4] @5963: 1234

--繼續：
SCOTT@test> update test set id='12345' where rowid='AAAQZYAAGAAAAAKAAA';
1 row updated.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> alter system checkpoint;
System altered.

BBED> x /rc
rowdata[0] @5951
----------
flag@5951: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5952: 0x02
cols@5953: 1

col 0[5] @5954: 12345

SCOTT@test> update test set id='1234567' where rowid='AAAQZYAAGAAAAAKAAA';
1 row updated.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> alter system checkpoint;
System altered.

--恩，奇怪並沒有出現我想象的情況。

BBED> x /rc
rowdata[0] @5930
----------
flag@5930: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5931: 0x02
cols@5932: 1

col 0[7] @5933: 1234567

4.重來看看：

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
------------------ ----------------------------------------------------------------------------------------------------
AAAQZdAAGAAAAAKAAA

SCOTT@test> @ &r/lookup_rowid AAAQZdAAGAAAAAKAAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
67165 6 10 0 6,10 alter system dump datafile 6 block 10 ;

SCOTT@test> update test set id='12345' where rowid='AAAQZdAAGAAAAAKAAA';
1 row updated.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> alter system checkpoint;
System altered.

BBED> set dba 6,10
DBA 0x0180000a (25165834 6,10)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @5977 0x20

BBED> x /rc
rowdata[0] @5977
----------
flag@5977: 0x20 (KDRHFH)
lock@5978: 0x02
cols@5979: 0
nrid@5980:0x0180000b.10a

--奇怪慢慢增加不行，也許更oracle內部演算法有關。bvi觀察到 20 02 00 01 80 00 0B 01 0A(9個位元組)，注意這裡rowid全面沒有長度指示器。

5.全部update看看：
SCOTT@test> update test set id='12345' ;
1000 rows updated.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> alter system checkpoint;
System altered.

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[4]                           @1578
ub1 rowdata[6606]                          @1582
ub4 tailchk                                @8188

--這樣僅僅剩下4自己。幾乎沒有剩下的空間。可以發現每條記錄都發生了行遷移。

BBED> p *kdbr[1]
rowdata[6588]
-------------
ub1 rowdata[6588] @8170 0x20

BBED> x /rc
rowdata[6588] @8170
-------------
flag@8170: 0x20 (KDRHFH)
lock@8171: 0x01
cols@8172: 0
nrid@8173:0x0180000b.10b

--從以上分析可以推出計算最大行號(Hakan Factor)按照剩餘空間/每條記錄9位元組來計算的。
--因為每個行目錄佔2個位元組。加上每條記錄9個位元組。
(8192-110-4)=8078/11=734.36

--為什麼少了幾條呢，我推測：
--9i下建表僅僅1個itl槽，而現在這樣有2個itl槽，注ctas建表有3個itl槽。這樣多出了24位元組。
--這樣算下來正好多處2條記錄。加起來正好是736，不過還是差1(行號從0開始).

8192-110-4+24=8102
8102/11=736.54

--我測試表空間是SEGMENT SPACE MANAGEMENT MANUAL。如果是SEGMENT SPACE MANAGEMENT AUTO，還要少1條，是733.
--大家可以在自行測試。有機會測試9i就清楚了。

[20150720]為什麼8K資料塊Hakan Factor=736

相關文章