[20160806]ROWDEPENDENCIES.txt

[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属性.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2123179/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2123179/