[20200814]8K数据库最大行号.txt
--//昨天听别人上课,对方想演示ITL槽不足出现的阻塞和死锁情况,讲到oracle 8K的数据块最大行号不能超过736.实际上
--//比较准确的提法是1块(数据块大小8k)最多仅仅容纳736条记录,实测733.估计采用mssm模式可以达到736条(注:我没测试).
--//我记忆里我以前做过一个例子演示行号可以超过这个限制,找了一下以前的测试,在12c上重复演示看看.
--//原始链接:http://blog.itpub.net/267265/viewspace-746749/=>[20121019]8k数据块到底能放多少行记录.txt
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
2.建立测试脚本:
drop table t2 purge ;
SCOTT@test01p> create table t2 (a number) pctfree 0;
Table created.
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 28720 28720 T2
--// SPARE1=736.
declare
v_newrowid rowid;
begin
for i in 1..4000 loop
insert into t2 values(null) returning rowid into v_newrowid;
delete from t2 where rowid=v_newrowid;
end loop;
insert into t2 values(NULL);
commit;
end;
/
--//在一个事务里面,插入1条记录占用1个行目录,再删除记录后,由于在一个事务里面,不会重用原来的行目录,这样最终可以确定最大的
--//行号.为了确定表T2占用的块地址,插入1条记录并提交.
3.测试:
--//执行以上脚本后:
SCOTT@test01p> alter system checkpoint ;
System altered.
SCOTT@test01p> select rowid,t2.* from t2;
ROWID A
------------------ ----------
AAAHAwAALAAAAE2AfB
SCOTT@test01p> @ rowid AAAHAwAALAAAAE2AfB
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
28720 11 310 1985 0x2C00136 11,310 alter system dump datafile 11 block 310
--//可以发现行号=1985.
SCOTT@test01p> alter table t2 minimize records_per_block;
Table altered.
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
------- ---------- -------------- -----------------
34753 28720 28720 T2
--// SPARE1=34753.
--// 34753-32768=1985,难道12c最大行号1985.具体看后面分析...
--//在这样的情况下建立位图索引,不会存在问题.
SCOTT@test01p> create bitmap index i_t2_a on t2(a);
Index created.
SCOTT@test01p> drop index i_t2_a ;
Index dropped.
--//通过bbed观察,注意bbed for windows查看时block要+1,主要原因是bbed for windows版本对应是9i的,无法识别12c的数据文件的
--//OS头,后面的测试数据块都要+1,不再另外说明.
BBED> map dba 11,311
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
Block: 311 Dba:0x02c00137
------------------------------------------------------------
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[1986] @118
ub1 freespace[75] @4090
ub1 rowdata[4023] @4165
ub4 tailchk @8188
--//sb2 kdbr[1986] ,也就是最大行号1985(从0开始计算).如果取消minimize records_per_block设置.
SQL> alter table t2 nominimize records_per_block;
Table altered.
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 28720 28720 T2
--//spare1=736.还原.再次建立位图索引看看.
SCOTT@test01p> create bitmap index i_t2_a on t2(a);
create bitmap index i_t2_a on t2(a)
*
ERROR at line 1:
ORA-28604: table too fragmented to build bitmap index (46137654,1985,744)
d:\>oerr ora 28604
28604, 00000, "table too fragmented to build bitmap index (%s,%s,%s)"
// *Cause: The table has one or more blocks that exceed the maximum number
// of rows expected when creating a bitmap index. This is probably
// due to deleted rows. The values in the message are:
// (data block address, slot number found, maximum slot allowed)
// *Action: Defragment the table or block(s). Use the values in the message
// to determine the FIRST block affected. (There may be others).
--//翻译: 该表有一个或多个块，超过创建位图索引时预期的最大行数。 这可能是由于删除了行。
--// 消息中的值是： (数据块地址、找到的插槽号、允许的最大插槽)
--//46137654 = set dba 11,310 = alter system dump datefile 11 block 310 = 0x2c00136,对应块地址.
--//第1,2个参数都能对上.而第3个参数是允许的最大插槽744,有点奇怪比736大8.
4.继续:
--//仔细看了原始链接,发现我现在改写的脚本有一点点问题,难道执行4000次循环.接着
--//插入的行目录在1985.minimize records_per_block具有回收行目录的功能吗? 我记忆当时测试最大2015,
--//但是以后重复测试无论如何最大2014.重复测试:
drop table t2 purge ;
create table t2 (a number) pctfree 0;
declare
v_newrowid rowid;
begin
for i in 1..4000 loop
insert into t2 values(null) returning rowid into v_newrowid;
delete from t2 where rowid=v_newrowid;
end loop;
end;
/
insert into t2 values(NULL);
commit ;
insert into t2 values(NULL);
rollback;
--//注:这里有点误操作...手快了....
alter system checkpoint ;
SCOTT@test01p> select rowid,t2.* from t2;
ROWID A
------------------ ----------
AAAHA7AALAAAAE2AfB
SCOTT@test01p> @ rowid AAAHA7AALAAAAE2AfB
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
28731 11 310 1985 0x2C00136 11,310 alter system dump datafile 11 block 310
--//注:我当前会话没有退出,应该还是插入在dba=11,310的位置.
BBED> map dba 11,311
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
Block: 311 Dba:0x02c00137
------------------------------------------------------------
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[1987] @118
ub1 freespace[70] @4092
ub1 rowdata[4026] @4162
ub4 tailchk @8188
--// sb2 kdbr[1987] @118,说明我当前的行号还可以增加,这样解析不通.这样前面4000-1985 = 2015,无法解析得通.
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
--//噢!注意看下划线,实际上在另外的块行号已经到2015,对于这个回话相当于该块满了,只能选择新的一块继续DML的执行.
--//这样可以解析先在dba=11,309插入2015条再删除.再在dba=11,310插入1985条.
--//但是还是我无法解析为什么链接http://blog.itpub.net/267265/viewspace-746749/,测试时看到最大2014.
declare
v_newrowid rowid;
begin
for i in 1..20000 loop
if i=2000 then
insert into t2 values(NULL);
else
insert into t2 values(null) returning rowid into v_newrowid;
delete from t2 where rowid=v_newrowid;
end if;
end loop;
end;
/
--//注以上脚本是我当时执行的脚本.继续观察当前的测试:
BBED> set dba 11,310
BBED> p kdbr
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[2006] @4130 4074
sb2 kdbr[2007] @4132 4072
sb2 kdbr[2008] @4134 4070
sb2 kdbr[2009] @4136 4068
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
--//发现1点点奇怪之处,前面的偏移开始相差2,而后面3个相差3.看看dba=11,310的情况:
BBED> p dba 11,311 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[1933] @3984 4220
sb2 kdbr[1934] @3986 4218
--//开始相差3.
sb2 kdbr[1935] @3988 4215
sb2 kdbr[1936] @3990 4212
sb2 kdbr[1937] @3992 4209
sb2 kdbr[1938] @3994 4206
sb2 kdbr[1939] @3996 4203
sb2 kdbr[1940] @3998 4200
sb2 kdbr[1941] @4000 4197
sb2 kdbr[1942] @4002 4194
sb2 kdbr[1943] @4004 4191
sb2 kdbr[1944] @4006 4188
sb2 kdbr[1945] @4008 4185
sb2 kdbr[1946] @4010 4182
sb2 kdbr[1947] @4012 4179
sb2 kdbr[1948] @4014 4176
sb2 kdbr[1949] @4016 4173
sb2 kdbr[1950] @4018 4170
sb2 kdbr[1951] @4020 4167
sb2 kdbr[1952] @4022 4164
sb2 kdbr[1953] @4024 4161
sb2 kdbr[1954] @4026 4158
sb2 kdbr[1955] @4028 4155
sb2 kdbr[1956] @4030 4152
sb2 kdbr[1957] @4032 4149
sb2 kdbr[1958] @4034 4146
sb2 kdbr[1959] @4036 4143
sb2 kdbr[1960] @4038 4140
sb2 kdbr[1961] @4040 4137
sb2 kdbr[1962] @4042 4134
sb2 kdbr[1963] @4044 4131
sb2 kdbr[1964] @4046 4128
sb2 kdbr[1965] @4048 4125
sb2 kdbr[1966] @4050 4122
sb2 kdbr[1967] @4052 4119
sb2 kdbr[1968] @4054 4116
sb2 kdbr[1969] @4056 4113
sb2 kdbr[1970] @4058 4110
sb2 kdbr[1971] @4060 4107
sb2 kdbr[1972] @4062 4104
sb2 kdbr[1973] @4064 4101
sb2 kdbr[1974] @4066 4098
sb2 kdbr[1975] @4068 4095
sb2 kdbr[1976] @4070 4092
sb2 kdbr[1977] @4072 4089
sb2 kdbr[1978] @4074 4086
sb2 kdbr[1979] @4076 4083
sb2 kdbr[1980] @4078 4080
sb2 kdbr[1981] @4080 4077
sb2 kdbr[1982] @4082 4074
sb2 kdbr[1983] @4084 4071
sb2 kdbr[1984] @4086 4068
sb2 kdbr[1985] @4088 4065
sb2 kdbr[1986] @4090 -1
--//可以确定oracle在dml时做了块内重整.修改了行目录的偏移量.这样可以容纳更多的行号.
--//也就是如果循环插入NULL再删除,这样在pctrfee=0的情况下看到的最大行号是2015.
5.看看minimize records_per_block后情况,是否会修改kdbr行目录数量.
SCOTT@test01p> alter table t2 minimize records_per_block;
Table altered.
SCOTT@test01p> alter system checkpoint ;
System altered.
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
------- ---------- -------------- -----------
34753 28731 28731 T2
--//34753-32767=1986.
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
--//并不会改变行目录数量.
BBED> map dba 11,311
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
Block: 311 Dba:0x02c00137
------------------------------------------------------------
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[1987] @118
ub1 freespace[70] @4092
ub1 rowdata[4026] @4162
ub4 tailchk @8188
--//有点乱...
总结:
--//1.这种情况在实际的生产环境很难遇到.
--//2.从前面建立位图索引时遇到ORA-28604,提示第3个参数是744,是否意味着oracle留有一定的余地.行号在736-744之间时建立位图索
--//引一样建立成功.补充测试看看.
ORA-28604: table too fragmented to build bitmap index (46137654,1985,744)
d:\>oerr ora 28604
28604, 00000, "table too fragmented to build bitmap index (%s,%s,%s)"
// *Cause: The table has one or more blocks that exceed the maximum number
// of rows expected when creating a bitmap index. This is probably
// due to deleted rows. The values in the message are:
// (data block address, slot number found, maximum slot allowed)
// *Action: Defragment the table or block(s). Use the values in the message
// to determine the FIRST block affected. (There may be others).
drop table t2 purge ;
create table t2 (a number) pctfree 0;
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 28740 28740 T2
--//SPARE1=736.
declare
v_newrowid rowid;
begin
for i in 1..743 loop
insert into t2 values(null) returning rowid into v_newrowid;
delete from t2 where rowid=v_newrowid;
end loop;
insert into t2 values(NULL);
commit;
end;
/
SCOTT@test01p> select rowid,t2.* from t2;
ROWID A
------------------ ----------
AAAHBEAALAAAAE1ALn
SCOTT@test01p> @ rowid AAAHBEAALAAAAE1ALn
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
28740 11 309 743 0x2C00135 11,309 alter system dump datafile 11 block 309
SCOTT@test01p> create bitmap index i_t2_a on t2(a);
Index created.
--//位图索引建立成功,而实际上行号=743.
SCOTT@test01p> alter table t2 minimize records_per_block;
alter table t2 minimize records_per_block
*
ERROR at line 1:
ORA-28602: statement not permitted on tables containing bitmap indexes
SCOTT@test01p> drop index i_t2_a;
Index dropped.
SCOTT@test01p> alter table t2 minimize records_per_block;
Table altered.
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
------- ---------- -------------- --------------------
33511 28740 28740 T2
--//33511-32767 = 744.
--//如果建立表后执行如下,就无法建立位图索引.
declare
v_newrowid rowid;
begin
for i in 1..744 loop
insert into t2 values(null) returning rowid into v_newrowid;
delete from t2 where rowid=v_newrowid;
end loop;
insert into t2 values(NULL);
commit;
end;
/
--//留给大家测试,我不做了.^_^.