[20190101]塊內重整.txt

--//我不知道用什麼術語表達這樣的情況,我僅僅一次開會對方這麼講,我現在也照用這個術語.

--//當dml插入資料到資料塊時,預留一定的空間(pctfree的百分比)不再插入.保留一些空間主要目的為了ITL的增加,以及update時空間增長.

--//避免大量的行遷移情況出現.

--//當到達頂部時,會出現一次塊內重整(也有叫塊內重組).透過演示瞭解這個過程.

1.環境:

SCOTT@test01p> @ ver1

PORT_STRING VERSION BANNER CON_ID

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

IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0

SCOTT@test01p> create table t as select rownum id,to_char(rownum)||lpad('x',800,'x') name from dual connect by level<=8;

Table created.

SCOTT@test01p> @desc t

Name Null? Type

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

ID NUMBER

NAME VARCHAR2(840)

--//name 定義VARCHAR2(840).

SCOTT@test01p> select rowid ,id from t;

ROWID ID

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

AAAF61AALAAAACrAAA 1

AAAF61AALAAAACrAAB 2

AAAF61AALAAAACrAAC 3

AAAF61AALAAAACrAAD 4

AAAF61AALAAAACrAAE 5

AAAF61AALAAAACrAAF 6

AAAF61AALAAAACrAAG 7

AAAF61AALAAAACrAAH 8

8 rows selected.

--//有8條記錄在一個塊中.

SCOTT@test01p> @rowid AAAF61AALAAAACrAAA

OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT

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

24245 11 171 0 0x2C000AB 11,171 alter system dump datafile 11 block 171

SCOTT@test01p> alter system checkpoint ;

System altered.

2.透過bbed觀察:

BBED> set dba 11,172

DBA 0x02c000ac (46137516 11,172)

--//windows版本block+1.

BBED> map

File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)

Block: 172 Dba:0x02c000ac

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

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[8] @142

ub1 freespace[1550] @158

ub1 rowdata[6480] @1708

ub4 tailchk @8188

BBED> p kdbr

sb2 kdbr[0] @142 7254

sb2 kdbr[1] @144 6444

sb2 kdbr[2] @146 5634

sb2 kdbr[3] @148 4824

sb2 kdbr[4] @150 4014

sb2 kdbr[5] @152 3204

sb2 kdbr[6] @154 2394

sb2 kdbr[7] @156 1584

BBED> x /rnc *kdbr[0]

rowdata[5670] @7378

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

flag@7378: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@7379: 0x00

cols@7380: 2

col 0[2] @7381: 1

col 1[801] @7384: 1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ..trunc.

--//注這裡的偏移是相對偏移 7254+124(kdbh地址) = 7378.

--//共佔用3+1+2+3+801 = 810,注前面有1個位元組保持長度指示器.另外注意字串長度大於250,需要使用3個位元組儲存長度指示器.

--//可以從資料的偏移量可以看出資料是從塊底開始插入的.當前空餘空間是1550.

3.繼續測試,刪除部分資料:

--//先刪除一部分資料,僅僅打上標識標識刪除.

SCOTT@test01p> delete from t where id in (1,3,6,7);

4 rows deleted.

SCOTT@test01p> commit ;

Commit complete.

SCOTT@test01p> alter system checkpoint ;

System altered.

--//透過bbed觀察:

BBED> map

File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)

Block: 172 Dba:0x02c000ac

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

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[8] @142

ub1 freespace[1550] @158

ub1 rowdata[6480] @1708

ub4 tailchk @8188

--//刪除部分記錄,freespace空間保持不變.

BBED> p kdbr

sb2 kdbr[0] @142 7254

sb2 kdbr[1] @144 6444

sb2 kdbr[2] @146 5634

sb2 kdbr[3] @148 4824

sb2 kdbr[4] @150 4014

sb2 kdbr[5] @152 3204

sb2 kdbr[6] @154 2394

sb2 kdbr[7] @156 1584

BBED> x /rnc *kdbr[0]

rowdata[5670] @7378

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

flag@7378: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)

lock@7379: 0x02

cols@7380: 0

--//僅僅flag從2c變成3c,加入了KDRHFD標識.

4.然後修改一部分資料看看:

SCOTT@test01p> update t set name=lpad('a',811,'a') where id=2;

1 row updated.

SCOTT@test01p> commit ;

Commit complete.

SCOTT@test01p> alter system checkpoint ;

System altered.

--//我修改的長度與原來不等,這樣增加長度增加10個位元組.這樣還剩下1550-820 = 730位元組.

--//驗證看看:

BBED> map

File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)

Block: 172 Dba:0x02c000ac

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

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[8] @142

ub1 freespace[730] @158

ub1 rowdata[7300] @888

ub4 tailchk @8188

--//ok正確!!

BBED> p kdbr

sb2 kdbr[0] @142 7254

sb2 kdbr[1] @144 764

sb2 kdbr[2] @146 5634

sb2 kdbr[3] @148 4824

sb2 kdbr[4] @150 4014

sb2 kdbr[5] @152 3204

sb2 kdbr[6] @154 2394

sb2 kdbr[7] @156 1584

--//kdbr[1] 指向新的位置.對應id=2的記錄.其它不動.

BBED> x /rnc *kdbr[1]

rowdata[0] @888

----------

flag@888: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@889: 0x03

cols@890: 2

col 0[2] @891: 2

col 1[811] @894: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa... trunc..

--//如果繼續修改記錄,長度消耗大於730並且長度與原來不一樣,這樣就會出現塊內重整的情況:

SCOTT@test01p> update t set name=lpad('b',811,'b') where id=4;

1 row updated.

SCOTT@test01p> commit ;

Commit complete.

SCOTT@test01p> alter system checkpoint ;

System altered.

BBED> map

File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)

Block: 172 Dba:0x02c000ac

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

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[8] @142

ub1 freespace[4762] @158

ub1 rowdata[3268] @4920

ub4 tailchk @8188

--//freespace變成了4762,出現一次塊內重整,而且收回刪除記錄的空間.

BBED> p kdbr

sb2 kdbr[0] @142 8062

sb2 kdbr[1] @144 7242

sb2 kdbr[2] @146 7240

sb2 kdbr[3] @148 4796

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~當前偏移最小.

sb2 kdbr[4] @150 6430

sb2 kdbr[5] @152 6428

sb2 kdbr[6] @154 6426

sb2 kdbr[7] @156 5616

--//可以發現行目錄發生變化對比前面的情況,做了整理,整體下移.我前面刪除的記錄是id in (1,3,6,7);

--//id=6,7的記錄應該對應kdbr[5],kdbr[6],可以發現記錄的偏移地址6428,6426,挨的很近.

--//可以大致猜測它的演算法,移動除kdbr[3](對應id=4)的記錄下移騰出空間,你可以發現kdbr[3]指向的偏移是當前最小的.

BBED> x /rnc *kdbr[5]

rowdata[1632] @6552

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

flag@6552: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)

lock@6553: 0x02

cols@6554: 0

BBED> x /rnc *kdbr[6]

rowdata[1630] @6550

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

flag@6550: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)

lock@6551: 0x02

cols@6552: 0

BBED> dump /v offset 6550 count 20

File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)

Block: 172 Offsets: 6550 to 6569 Dba:0x02c000ac

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

3c023c02 2c000202 c106fe21 03357878 78787878 l <.<.,...??.5xxxxxx

<32 bytes per line>

--//僅僅保留2個位元組.

BBED> x /rnc *kdbr[0]

rowdata[3266] @8186

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

flag@8186: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)

lock@8187: 0x02

cols@8188: 0

BBED> dump /v count 20

File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)

Block: 172 Offsets: 8186 to 8191 Dba:0x02c000ac

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

3c020106 ff18 l <.....

<32 bytes per line>

--//這個時候按照以前使用bbed修復刪除記錄的方法是無用的.因為對應的記錄資訊已經被覆蓋了.

5.繼續插入記錄看看.

SCOTT@test01p> insert into t values(9,to_char(9)||lpad('y',800,'y'));

1 row created.

SCOTT@test01p> commit ;

Commit complete.

SCOTT@test01p> alter system checkpoint ;

System altered.

--//當前塊已經空出許多空間,可以繼續插入資料,看看這時的情況:

BBED> map

File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)

Block: 172 Dba:0x02c000ac

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

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[9] @142

ub1 freespace[3950] @160

ub1 rowdata[4078] @4110

ub4 tailchk @8188

--//還有freespace=3950.

BBED> p kdbr

sb2 kdbr[0] @142 2

sb2 kdbr[1] @144 7242

sb2 kdbr[2] @146 5

sb2 kdbr[3] @148 4796

sb2 kdbr[4] @150 6430

sb2 kdbr[5] @152 6

sb2 kdbr[6] @154 -1

sb2 kdbr[7] @156 5616

sb2 kdbr[8] @158 3986

--//可以發現當前塊又插入1條.

BBED> x /rnc *kdbr[8]

rowdata[0] @4110

----------

flag@4110: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@4111: 0x02

cols@4112: 2

col 0[2] @4113: 9

col 1[801] @4116: 9yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy..trunc..

--//注意看前面的kdbr[0],kdbr[2],kdbr[5],kdbr[6]對應的偏移量(2,5,6,-1),透過偏移量連結起來.

--//也就是這個這時的偏移量指向的地址根本不是rowdate區域.

--//我不知道是否可以得出這樣結論:如果kdbr指向的值如果小於當前的行目錄數量(9),這些行對應的記錄應該是刪除的.

--//理論講這時對應記錄很難恢復,已經覆蓋了.

6.測試重用的情況:

--//是否會重用呢?理論講應該會重用.開啟新的會話看看:

SCOTT@test01p> insert into t values(10,to_char(10)||lpad('x',800,'x'));

1 row created.

SCOTT@test01p> commit ;

Commit complete.

SCOTT@test01p> alter system checkpoint;

System altered.

BBED> map

File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)

Block: 172 Dba:0x02c000ac

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

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[9] @142

ub1 freespace[3139] @160

ub1 rowdata[4889] @3299

ub4 tailchk @8188

--//freespace=3139.

BBED> p kdbr

sb2 kdbr[0] @142 3175

sb2 kdbr[1] @144 7242

sb2 kdbr[2] @146 5

sb2 kdbr[3] @148 4796

sb2 kdbr[4] @150 6430

sb2 kdbr[5] @152 6

sb2 kdbr[6] @154 -1

sb2 kdbr[7] @156 5616

sb2 kdbr[8] @158 3986

--//理論講不同的會話不會插入相同的塊,因為我建表比較特殊,使用ctas建立的.

--//可以發現插入佔用了kdbr[0].

BBED> x /rnc *kdbr[0]

rowdata[0] @3299

----------

flag@3299: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@3300: 0x03

cols@3301: 2

col 0[2] @3302: 10

col 1[802] @3305: 10xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx...trunc..

SCOTT@test01p> select rowid,id from t;

ROWID ID

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

AAAF61AALAAAACrAAA 10

AAAF61AALAAAACrAAB 2

AAAF61AALAAAACrAAD 4

AAAF61AALAAAACrAAE 5

AAAF61AALAAAACrAAH 8

AAAF61AALAAAACrAAI 9

6 rows selected.

--//oracle在插入時能快速定位到kdbr[0],估計在塊內有相關記錄.

BBED> p kdbh

struct kdbh, 14 bytes @124

ub1 kdbhflag @124 0x00 (NONE)

b1 kdbhntab @125 1

b2 kdbhnrow @126 9

sb2 kdbhfrre @128 2

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

sb2 kdbhfsbo @130 36

sb2 kdbhfseo @132 3175

b2 kdbhavsp @134 3147

b2 kdbhtosp @136 3147

--//猜測記錄在kdbh.kdbhfrre中,當前是2.如果繼續插入,變成5就可以驗證我的判斷。

--//注意在新的會話插入(session 2)：

SCOTT@test01p> insert into t values(11,to_char(11)||lpad('w',800,'w'));

1 row created.

SCOTT@test01p> commit ;

Commit complete.

SCOTT@test01p> alter system checkpoint;

System altered.

BBED> map

File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)

Block: 172 Dba:0x02c000ac

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

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[9] @142

ub1 freespace[2328] @160

ub1 rowdata[5700] @2488

ub4 tailchk @8188

--//freespace繼續減少。

BBED> p kdbr

sb2 kdbr[0] @142 3175

sb2 kdbr[1] @144 7242

sb2 kdbr[2] @146 2364

sb2 kdbr[3] @148 4796

sb2 kdbr[4] @150 6430

sb2 kdbr[5] @152 6

sb2 kdbr[6] @154 -1

sb2 kdbr[7] @156 5616

sb2 kdbr[8] @158 3986

--//插入記錄佔用了kdbr[2]。

BBED> p kdbh

struct kdbh, 14 bytes @124

ub1 kdbhflag @124 0x00 (NONE)

b1 kdbhntab @125 1

b2 kdbhnrow @126 9

sb2 kdbhfrre @128 5

--//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~變成5，驗證的判斷。

sb2 kdbhfsbo @130 36

sb2 kdbhfseo @132 2364

b2 kdbhavsp @134 2336

b2 kdbhtosp @136 2336

--//這樣透過kdbr[5],裡面記錄的是6，繼續插入可以很容易行目錄，最後記錄-1,表示已經沒有了。

SCOTT@test01p> select rowid,id from t;

ROWID ID

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

AAAF61AALAAAACrAAA 10

AAAF61AALAAAACrAAB 2

AAAF61AALAAAACrAAC 11

AAAF61AALAAAACrAAD 4

AAAF61AALAAAACrAAE 5

AAAF61AALAAAACrAAH 8

AAAF61AALAAAACrAAI 9

7 rows selected.

總結:

--//僅僅透過一些簡單的例子演示這個過程,也許許多情況下更加複雜.

[20190101]塊內重整.txt

相關文章