[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2795046/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261105]测试21c SQL Translation Framework.txt
- [20261106]ANSI DATE and TIMESTAMP SQL syntax.txt
- [20261106]光标跟踪事件11.txt
- [20261102]光标跟踪事件9.txt
- [20261102]光标跟踪事件8.txt
- [20261102]为什么出现光标出现TOP_LEVEL_RPI_CURSOR不共享.txt
- [20261102]改变pdb名字.txt
- [20261018]光标跟踪事件3.txt
- [20261016]光标跟踪事件2.txt
- [20261015]模拟kksfbc child completion等待事件2.txt
