欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

[20190101]块内重整.txt

程序员文章站 2022-03-23 08:52:30
[20190101]块内重整.txt--//我不知道用什么术语表达这样的情况,我仅仅一次开会对方这么讲,我现在也照用这个术语.--//当dml插入数据到数据块时,预留一定的空间(pctfree的百分比)不再插入.保留一些空间主要目的为了ITL的增加,以及update时空间增长.--//避免大量的行迁 ......

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

总结:
--//仅仅通过一些简单的例子演示这个过程,也许许多情况下更加复杂.