[20211009]8K資料庫最大行號補充.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20200814]8K資料庫最大行號.txt資料庫
- [20181128]toad連線資料庫的問題(補充).txt資料庫
- mysql資料庫常用命令（補充）MySql資料庫
- [20180928]ora-01426(補充).txt
- [20241016]Oracle C functions annotations補充.txtOracleFunction
- [20211215]提示precompute_subquery補充.txt
- [20220603]測試quiz night(補充).txtUI
- Django筆記三十七之多資料庫操作(補充版)Django筆記資料庫
- 並推出了關係型資料庫功能來補充其核心NoSQL資料庫功能資料庫SQL
- [20230303]生成相關備庫的awr報表(補充說明).txt
- [20211116]plsql_code_type=native補充.txtSQL
- [20211221]提示precompute_subquery補充2.txt
- [20210803]使用那個shared pool latch(補充).txt
- [20211013]測試遠端監聽補充.txt
- [20221014]TNS-12543 TNSdestination host unreachable(補充).txt
- [20180129]簡單探究cluster table(補充)4.txt
- [20181229]簡單探究cluster table(補充)3.txt
- [20181227]簡單探究cluster table(補充)2.txt
- [20220119]超長sql語句補充3.txtSQL
- [20220120]超長sql語句補充4.txtSQL
- [20211111]補充完善ash_wait_chains指令碼.txtAI指令碼
- [20211025]12c sequence nocache測試補充.txt
- [20211009]使用bash計算sql語句的sql_id.txtSQL
- [20230223]8k資料塊建立最大檔案是多少(ORA-03206).txt
- 【UniApp】-uni-app-資料傳遞補充APP
- [20200414]Linux下快速刪除大量檔案(補充).txtLinux
- [20190214]11g Query Result Cache RC Latches補充.txt
- [20190211]簡單測試埠是否開啟(補充).txt
- [20211221]記錄使用sqlplus的小問題補充.txtSQL
- [20211021]windows新建文字檔案帶日期(補充).txtWindows
- [20220309]查詢x$ksmmem遇到的疑問補充.txt
- [20201208]為什麼返回2行記錄補充.txt
- 分散式資料庫火了 開源填補資料庫空白分散式資料庫
- [20210428]資料庫連線加密.txt資料庫加密
- [20190329]探究sql語句相關mutexes補充2.txtSQLMutex
- [20190312]關於增量檢查點的疑問(補充).txt
- [20201116]測試CURSOR_SPACE_FOR_TIME(10g)(補充).txt
- [20220317]補充完善TPT 顯示欄位列的指令碼.txt指令碼