[20211009]8K資料庫最大行號補充.txt

lfree發表於2021-10-09

[20211009]8K資料庫最大行號補充.txt

--//別人問的問題不知道是我沒講明白,還是不好理解,裡面提到kdbr指向的偏移開始相差2,而後面3個相差3.
--//我的意思是oracle在dml時做了塊內重整.修改了行目錄的偏移量.這樣可以容納更多的行號.
--//也就是如果迴圈插入NULL再刪除,這樣在pctrfee=0的情況下看到的最大行號是2015.
-
--//另外的問題,為什麼不再繼續插入,ub1 freespace[7],還剩下7個位元組,按照對方的理解插入1條記錄需要5個位元組(行目錄佔2個位元組)
--//插入null值,rowdate區需佔3個位元組,實際上不能這樣算,oracle為了預留行遷移空間,而保留6個位元組給rowid.
--//插入1條記錄至少需要11位元組,行目錄2位元組+行前面部分3位元組+rowid佔6位元組.這樣freespace=7以及塊內重整回收3個位元組=10個位元組
--//依舊不夠的.

--//參考連結:http://blog.itpub.net/267265/viewspace-2712438/=>[20200814]8K資料庫最大行號.txt

BBED> p kdbr
sb2 kdbr[0]                                 @118      8086
sb2 kdbr[1]                                 @120      8084
sb2 kdbr[2]                                 @122      8082
sb2 kdbr[3]                                 @124      8080
sb2 kdbr[4]                                 @126      8078
sb2 kdbr[5]                                 @128      8076
sb2 kdbr[6]                                 @130      8074
sb2 kdbr[7]                                 @132      8072
sb2 kdbr[8]                                 @134      8070
sb2 kdbr[9]                                 @136      8068
...
sb2 kdbr[2010]                              @4138     4066
sb2 kdbr[2011]                              @4140     4064
sb2 kdbr[2012]                              @4142     4061
sb2 kdbr[2013]                              @4144     4058
sb2 kdbr[2014]                              @4146     4055
--//偏移開始相差2,而後面3個相差3.

BBED> map dba 11,310
 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
 Block: 310                                   Dba:0x02c00136
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @100
 struct kdbt[1], 4 bytes                    @114
 sb2 kdbr[2015]                             @118
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 ub1 freespace[7]                           @4148
 ub1 rowdata[4033]                          @4155
 ub4 tailchk                                @8188

--//可以參考我以前寫的 [20150720]為什麼8K資料塊Hakan Factor=736.txt=>http://blog.itpub.net/267265/viewspace-1742243/
--//一些細節我自己都忘記了,而且這種特例在真實的生產系統根本不可能遇到。重複測試,看看freespace空間的變化過程。

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立測試指令碼:

SCOTT@book> create table t2 (a varchar2(1)) SEGMENT CREATION IMMEDIATE pctfree 0;
Table created.

--//注:我以前測試使用number,這次測試使用varchar2(1)。

SELECT tab.spare1, obj.object_id, obj.data_object_id, obj.object_name
  FROM SYS.tab$ tab, dba_objects obj
 WHERE tab.obj# = obj.object_id AND tab.dataobj# = obj.data_object_id AND obj.owner = USER AND obj.object_name = 'T2';

    SPARE1  OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- ---------- -------------- --------------------
       736      93799          93799 T2
 
$ cat test1.txt
select systimestamp from dual;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;

declare
 v_newrowid rowid;
begin
 for i in 1..1608 loop
   insert into t2 values(null) returning rowid into v_newrowid;
   delete from t2 where rowid=v_newrowid;
 end loop;
end;
/

declare
 v_newrowid rowid;
begin
 for i in 1..406 loop
   insert into t2 values(null) returning rowid into v_newrowid;
   delete from t2 where rowid=v_newrowid;
   execute immediate 'alter system checkpoint';
   sys.dbms_lock.sleep(0.2);
 end loop;
 insert into t2 values(NULL);
 commit;
end;
/
select systimestamp from dual;
alter system checkpoint;

--//在一個事務裡面,插入1條記錄佔用1個行目錄,再刪除記錄後,由於在一個事務裡面,不會重用原來的行目錄,這樣最終可以確定最大的
--//行號.為了確定表T2佔用的塊地址,插入1條記錄並提交.
--//注:我以前測試像以上情況最大行號=2015,2015-1=2014,迴圈次數修改為2014最後插入1條,方便定位塊地址。
--//開啟另外視窗執行如下,裡面的dba 4,740可以先註解sleep等內容執行1次來確定.然後刪除表重建.
--//先執行如下,然後在執行test1.txt指令碼.

$ seq 900 | xargs -IQ echo "echo map dba 4,740 | bbed  parfile=bbed.par cmdfile=cmd.par | grep freespace | ts.awk; sleep 0.2"| bash | tee /tmp/test1.txt

3.測試:
SCOTT@book> @ test1.txt
SYSTIMESTAMP
---------------------------------------------------------------------------
2021-10-09 09:39:57.101788 +08:00
System altered.
System altered.
System altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SYSTIMESTAMP
---------------------------------------------------------------------------
2021-10-09 09:41:20.549225 +08:00
System altered.

--//中斷視窗2的執行過程.

$ vi /tmp/test1.txt
[2021-10-09 09:39:57]  ub1 freespace[1257]                        @3444
[2021-10-09 09:39:57]  ub1 freespace[25]                          @3336
[2021-10-09 09:39:57]  ub1 freespace[20]                          @3338
[2021-10-09 09:39:57]  ub1 freespace[15]                          @3340
[2021-10-09 09:39:58]  ub1 freespace[10]                          @3342
[2021-10-09 09:39:58]  ub1 freespace[5]                           @3344
[2021-10-09 09:39:58]  ub1 freespace[0]                           @3346
--//開始插入1608行再刪除,發現自由空間迅速從25掉入0,然後回收到1609,可以看出偏移每次下移2個單位.因為行目錄增加1行.
--//freespace空間每次減少5個單位.
[2021-10-09 09:39:58]  ub1 freespace[1609]                        @3348
[2021-10-09 09:39:58]  ub1 freespace[1604]                        @3350
[2021-10-09 09:39:59]  ub1 freespace[1599]                        @3352
[2021-10-09 09:39:59]  ub1 freespace[1589]                        @3356
[2021-10-09 09:39:59]  ub1 freespace[1584]                        @3358
[2021-10-09 09:39:59]  ub1 freespace[1579]                        @3360
...,...
[2021-10-09 09:41:04]  ub1 freespace[14]                          @3986
[2021-10-09 09:41:04]  ub1 freespace[9]                           @3988
[2021-10-09 09:41:04]  ub1 freespace[4]                           @3990
--//再次回收
[2021-10-09 09:41:04]  ub1 freespace[321]                         @3992
[2021-10-09 09:41:04]  ub1 freespace[316]                         @3994
[2021-10-09 09:41:05]  ub1 freespace[311]                         @3996
[2021-10-09 09:41:05]  ub1 freespace[306]                         @3998
[2021-10-09 09:41:05]  ub1 freespace[301]                         @4000
[2021-10-09 09:41:05]  ub1 freespace[296]                         @4002
[2021-10-09 09:41:06]  ub1 freespace[286]                         @4006
[2021-10-09 09:41:06]  ub1 freespace[281]                         @4008
[2021-10-09 09:41:06]  ub1 freespace[276]                         @4010
[2021-10-09 09:41:06]  ub1 freespace[271]                         @4012
[2021-10-09 09:41:06]  ub1 freespace[266]                         @4014
[2021-10-09 09:41:07]  ub1 freespace[261]                         @4016
[2021-10-09 09:41:07]  ub1 freespace[256]                         @4018
[2021-10-09 09:41:07]  ub1 freespace[251]                         @4020
[2021-10-09 09:41:07]  ub1 freespace[246]                         @4022
[2021-10-09 09:41:08]  ub1 freespace[241]                         @4024
[2021-10-09 09:41:08]  ub1 freespace[236]                         @4026
[2021-10-09 09:41:08]  ub1 freespace[226]                         @4030
[2021-10-09 09:41:08]  ub1 freespace[221]                         @4032
[2021-10-09 09:41:08]  ub1 freespace[216]                         @4034
[2021-10-09 09:41:09]  ub1 freespace[211]                         @4036
[2021-10-09 09:41:09]  ub1 freespace[206]                         @4038
[2021-10-09 09:41:09]  ub1 freespace[201]                         @4040
[2021-10-09 09:41:09]  ub1 freespace[196]                         @4042
[2021-10-09 09:41:10]  ub1 freespace[191]                         @4044
[2021-10-09 09:41:10]  ub1 freespace[186]                         @4046
[2021-10-09 09:41:10]  ub1 freespace[181]                         @4048
[2021-10-09 09:41:10]  ub1 freespace[176]                         @4050
[2021-10-09 09:41:10]  ub1 freespace[171]                         @4052
[2021-10-09 09:41:11]  ub1 freespace[166]                         @4054
[2021-10-09 09:41:11]  ub1 freespace[161]                         @4056
[2021-10-09 09:41:11]  ub1 freespace[151]                         @4060
[2021-10-09 09:41:11]  ub1 freespace[146]                         @4062
[2021-10-09 09:41:12]  ub1 freespace[141]                         @4064
[2021-10-09 09:41:12]  ub1 freespace[136]                         @4066
[2021-10-09 09:41:12]  ub1 freespace[131]                         @4068
[2021-10-09 09:41:12]  ub1 freespace[126]                         @4070
[2021-10-09 09:41:12]  ub1 freespace[121]                         @4072
[2021-10-09 09:41:13]  ub1 freespace[116]                         @4074
[2021-10-09 09:41:13]  ub1 freespace[111]                         @4076
[2021-10-09 09:41:13]  ub1 freespace[106]                         @4078
[2021-10-09 09:41:13]  ub1 freespace[96]                          @4082
[2021-10-09 09:41:14]  ub1 freespace[91]                          @4084
[2021-10-09 09:41:14]  ub1 freespace[86]                          @4086
[2021-10-09 09:41:14]  ub1 freespace[81]                          @4088
[2021-10-09 09:41:14]  ub1 freespace[76]                          @4090
[2021-10-09 09:41:14]  ub1 freespace[71]                          @4092
[2021-10-09 09:41:15]  ub1 freespace[66]                          @4094
[2021-10-09 09:41:15]  ub1 freespace[61]                          @4096
[2021-10-09 09:41:15]  ub1 freespace[56]                          @4098
[2021-10-09 09:41:15]  ub1 freespace[51]                          @4100
[2021-10-09 09:41:15]  ub1 freespace[46]                          @4102
[2021-10-09 09:41:16]  ub1 freespace[41]                          @4104
[2021-10-09 09:41:16]  ub1 freespace[36]                          @4106
[2021-10-09 09:41:16]  ub1 freespace[31]                          @4108
[2021-10-09 09:41:16]  ub1 freespace[21]                          @4112
[2021-10-09 09:41:17]  ub1 freespace[16]                          @4114
[2021-10-09 09:41:17]  ub1 freespace[11]                          @4116
[2021-10-09 09:41:17]  ub1 freespace[6]                           @4118
[2021-10-09 09:41:17]  ub1 freespace[1]                           @4120
--//再次回收
[2021-10-09 09:41:18]  ub1 freespace[61]                          @4122
[2021-10-09 09:41:18]  ub1 freespace[56]                          @4124
[2021-10-09 09:41:18]  ub1 freespace[51]                          @4126
[2021-10-09 09:41:18]  ub1 freespace[46]                          @4128
[2021-10-09 09:41:18]  ub1 freespace[41]                          @4130
[2021-10-09 09:41:19]  ub1 freespace[31]                          @4134
[2021-10-09 09:41:19]  ub1 freespace[26]                          @4136
[2021-10-09 09:41:19]  ub1 freespace[21]                          @4138
[2021-10-09 09:41:19]  ub1 freespace[16]                          @4140
[2021-10-09 09:41:19]  ub1 freespace[11]                          @4142
[2021-10-09 09:41:17]  ub1 freespace[6]                           @4118
[2021-10-09 09:41:17]  ub1 freespace[1]                           @4120
[2021-10-09 09:41:18]  ub1 freespace[61]                          @4122
[2021-10-09 09:41:18]  ub1 freespace[56]                          @4124
[2021-10-09 09:41:18]  ub1 freespace[51]                          @4126
[2021-10-09 09:41:18]  ub1 freespace[46]                          @4128
[2021-10-09 09:41:18]  ub1 freespace[41]                          @4130
[2021-10-09 09:41:19]  ub1 freespace[31]                          @4134
[2021-10-09 09:41:19]  ub1 freespace[26]                          @4136
[2021-10-09 09:41:19]  ub1 freespace[21]                          @4138
[2021-10-09 09:41:19]  ub1 freespace[16]                          @4140
[2021-10-09 09:41:19]  ub1 freespace[11]                          @4142
[2021-10-09 09:41:20]  ub1 freespace[6]                           @4144
[2021-10-09 09:41:20]  ub1 freespace[1]                           @4146
--//再次回收
[2021-10-09 09:41:20]  ub1 freespace[9]                           @4148
[2021-10-09 09:41:20]  ub1 freespace[9]                           @4148
[2021-10-09 09:41:21]  ub1 freespace[9]                           @4148
--//不再插入.

4.透過bbed觀察:
SCOTT@book> alter system checkpoint ;
System altered.

SCOTT@book> select rowid,t2.* from t2;
ROWID              A
------------------ -
AAAW5nAAEAAAALkAfe

SCOTT@book> @ rowid AAAW5nAAEAAAALkAfe
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     93799          4        740       2014  0x10002E4           4,740                alter system dump datafile 4 block 740 ;
--//可以發現行號=2014.

BBED> set dba 4,740
        DBA             0x010002e4 (16777956 4,740)

BBED> p kdbr
sb2 kdbr[0]                                 @118      8086
sb2 kdbr[1]                                 @120      8084
sb2 kdbr[2]                                 @122      8082
sb2 kdbr[3]                                 @124      8080
sb2 kdbr[4]                                 @126      8078
sb2 kdbr[5]                                 @128      8076
sb2 kdbr[6]                                 @130      8074
...
sb2 kdbr[2008]                              @4134     4070
sb2 kdbr[2009]                              @4136     4068
sb2 kdbr[2010]                              @4138     4066
sb2 kdbr[2011]                              @4140     4064
sb2 kdbr[2012]                              @4142     4062
sb2 kdbr[2013]                              @4144     4060
sb2 kdbr[2014]                              @4146     4057
--//偏移開始相差2,而後面1個相差3.

BBED> map
 File: /mnt/ramdisk/book/users01.dbf (4)
 Block: 740                                   Dba:0x010002e4
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @100
 struct kdbt[1], 4 bytes                    @114
 sb2 kdbr[2015]                             @118
 ub1 freespace[9]                           @4148
 ub1 rowdata[4031]                          @4157
 ub4 tailchk                                @8188


BBED> x /rx *kdbr[1]
rowdata[4027]                               @8184
-------------
flag@8184: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8185: 0x01
cols@8186:    0
~~~~~~~~~~~~~~~~~~


BBED> x /rx *kdbr[0]
rowdata[4029]                               @8186
-------------
flag@8186: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
lock@8187: 0x01
cols@8188:    0

--//注意上下輸出結合看,實際上offset=8186,前面顯示是cols@8186:    0,後面顯示的是flag@8186: 0x3c (KDRHFL, KDRHFF, KDRHFD,
--//KDRHFH).很明顯這個是bbed bug.很明顯bbed設計者沒有考慮這種非常特殊的情況.

BBED> dump offset 8184 count 4
 File: /mnt/ramdisk/book/users01.dbf (4)
 Block: 740            Offsets: 8184 to 8187   Dba:0x010002e4
--------------------------------
 3c013c01
<128 bytes per line>
--//明顯每個記錄僅僅佔2個位元組.


BBED> x /rx *kdbr[2014]
rowdata[0]                                  @4157
----------
flag@4157: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4158: 0x01
cols@4159:    0

BBED> x /rx *kdbr[2013]
rowdata[3]                                  @4160
----------
flag@4160: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@4161: 0x01
cols@4162:    0

BBED> dump offset 4157 count 6
 File: /mnt/ramdisk/book/users01.dbf (4)
 Block: 740       Offsets: 4157 to 4162   Dba:0x010002e4
--------------------------------
 2c01003c 013c
<128 bytes per line>

--//明顯每個記錄僅僅佔3個位元組.

$ echo -e "set dba 4,740\np kdbr" | rlbbed | tr -d '\r' |grep kdbr | \
> awk 'NR==1{a=$NF;b=0;c=$0} NR>1 {if (a-$NF!=b &&a-$NF!=2) {print c;print $0,a-$NF};b=a-$NF;a=$NF;c=$0}'
sb2 kdbr[2013]                              @4144     4060
sb2 kdbr[2014]                              @4146     4057 3

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2795046/,如需轉載,請註明出處,否則將追究法律責任。

相關文章