[20160806]ROWDEPENDENCIES.txt
---今天看一些文档,突然想起几天前做的行迁移与itl槽的测试:
http://blog.itpub.net/267265/viewspace-2122712/
[20150720]为什么8K数据块Hakan Factor=736
http://blog.itpub.net/267265/viewspace-1742243/
--大家知道oracle如果一块发生dml操作,有1个伪列ora_rowscn记录最后的scn,但是不能通过这个定位对那条记录进行了操作.
--但是如果表定义使用ROWDEPENDENCIES,这样每行都会记录一个scn,scn占48位,需要6个字节保存这个信息.这样就在需要消耗
--6个字节加上行迁移需要保留rowid信息,这样6+6+3+2=17(6 scn+ 6 rowid+ 3 flag,lock,cols+2 行目录).
--测试看看:
1.建立测试环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t (id number,pad varchar2(200)) ROWDEPENDENCIES;
alter table t pctfree 0;
create unique index pk_t on t(id);
alter table t add constraint pk_t primary key (id) enable validate;
insert into t(id) select rownum from dual connect by level<=734;
commit ;
2.测试:
SELECT obj#, dataobj#, spare1
FROM sys.tab$
WHERE obj# IN (SELECT object_id
FROM dba_objects
WHERE owner = USER AND object_name in ('T','DEPT'));
OBJ# DATAOBJ# SPARE1
---------- ---------- ----------
105250 105250 476
92285 92285 736
--^_^,spare1=476,说明在表设置ROWDEPENDENCIES的情况下最大1块(8k).仅仅能保存476条记录.
SCOTT@test01p> select rowid,t.* from t where id=1;
ROWID ID PAD
------------------ ---------- -------------------------
AAAZsiAAJAAAACOAAA 1
SCOTT@test01p> @ rowid AAAZsiAAJAAAACOAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
105250 9 142 0 9,142 alter system dump datafile 9 block 142 ;
--通过bbed观察:
BBED> set dba 9,143
DBA 0x0240008f (37748879 9,143)
--//注我bbed使用for windows的版本,存在1个偏移.以下不在说明.
BBED> map
File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF (9)
Block: 143 Dba:0x0240008f
------------------------------------------------------------
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[474] @118
ub1 freespace[1063] @1066
ub1 rowdata[6059] @2129
ub4 tailchk @8188
-- (8192-4-118)/(6+6+3+2)=474.7058823529411764758, 大约474与476非常接近.我估计oracle计算仅仅考虑1个itl槽.这样计算修改如下
-- (8192-4-118+24)/(6+6+3+2)=476.1176475882352941176, 基本接近476.
-- sb2 kdbr[474] @118
-- //说明这个块仅仅保存了474条记录.与实际的测试非常接近.
-- //关于行迁移的测试不作了,没有意思.
3.看一些细节:
BBED> x /r *kdbr[1]
rowdata[2857] @4986
-------------
flag@4986: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4987: 0x01
cols@4988: 1
col 0[2] @4995: 0xc1 0x03
BBED> x /r *kdbr[0]
rowdata[2845] @4974
-------------
flag@4974: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4975: 0x01
cols@4976: 1
col 0[2] @4983: 0xc1 0x02
--bbed 不显示scn的相关信息.不过cols在4976偏移,而col0在4983,相减7. col0 前面有1个长度指示期,也就是两个之间保存的就是scn.
--//做一个转储看看:
SCOTT@test01p> alter system dump datafile 9 block 142 ;
System altered.
block_row_dump:
tab 0, row 0, @0x130a
tl: 12 fb: --H-FL-- lb: 0x1 cc: 1
dscn 0x0000.00000000
~~~~~~~~~~~~~~~~~~~~~~
col 0: [ 2] c1 02
tab 0, row 1, @0x1316
tl: 12 fb: --H-FL-- lb: 0x1 cc: 1
dscn 0x0000.00000000
col 0: [ 2] c1 03
--//注意~,当前是0,scn信息还保存在ITL槽中.
4.对比没有ROWDEPENDENCIES的表
SCOTT@test01p> select rowid from dept where rownum=1;
ROWID
------------------
AAAWh9AAJAAAACFAAA
SCOTT@test01p> @ rowid AAAWh9AAJAAAACFAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
92285 9 133 0 9,133 alter system dump datafile 9 block 133 ;
BBED> set dba 9,134
DBA 0x02400086 (37748870 9,134)
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x02400085
ub4 bas_kcbh @8 0x0166659d
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x02
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0x054b
ub2 spare3_kcbh @18 0x0000
--对比有ROWDEPENDENCIES的表:
BBED> set dba 9,143
DBA 0x0240008f (37748879 9,143)
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x0240008e
ub4 bas_kcbh @8 0x016aad8f
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x06 (KCBHFDLC, KCBHFCKV)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ub2 chkval_kcbh @16 0xb996
ub2 spare3_kcbh @18 0x0000
--注意看~, flg_kcbh记录的信息,有ROWDEPENDENCIES的表多1个KCBHFDLC属性.
