欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

数据块内部偏移量的基本计算方法

程序员文章站 2022-05-23 15:30:23
...

转载请注明出处:http://blog.csdn.net/guoyjoe/article/details/32715157 BASE的计算方法为: gyj@ZMDB select * from v$type_size where component in (KCB,KTB);COMPONEN TYPE DESCRIPTION TYPE_SIZE-------- -------- --------------------------------

转载请注明出处:http://blog.csdn.net/guoyjoe/article/details/32715157

BASE的计算方法为:

gyj@ZMDB> select * from v$type_size where component in ('KCB','KTB');

COMPONEN TYPE     DESCRIPTION                       TYPE_SIZE
-------- -------- -------------------------------- ----------
KCB      KCBH     BLOCK COMMON HEADER                      20
KTB      KTBIT    TRANSACTION VARIABLE HEADER              24
KTB      KTBBH    TRANSACTION FIXED HEADER                 48
KTB      KTBBH_BS TRANSACTION BLOCK BITMAP SEGMENT          8

1、我们先对ASSM做测试

yj@ZMDB> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

gyj@ZMDB> create tablespace assm datafile '/u01/app/oracle/oradata/zmdb/assm01.dbf' size 50M;

Tablespace created.

gyj@ZMDB> create table gyj_t5(id int,name varchar2(100))  tablespace assm;

Table created.

gyj@ZMDB> insert into gyj_t5 values(1,'AAAAA');

1 row created.

gyj@ZMDB> insert into gyj_t5 values(2,'BBBBB');

1 row created.

gyj@ZMDB> insert into gyj_t5 values(3,'CCCCC');

1 row created.

gyj@ZMDB> COMMIT;

Commit complete.

gyj@ZMDB> alter system flush buffer_cache;

System altered.

gyj@ZMDB> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,id,name from gyj_t5;

     FILE#     BLOCK#          ID   NAME
---------- ----------   ---------- ----------
        10        135          1    AAAAA
        10        135          2    BBBBB
        10        135          3    CCCCC


BBED> set file 10 block 135
        FILE#           10
        BLOCK#          135

BBED> p kdbr[0]
sb2 kdbr[0]                                 @118      8076

BBED> p *kdbr[0]
rowdata[24]
-----------
ub1 rowdata[24]                             @8176     0x2c

BBED> x /rnc
rowdata[24]                                 @8176    
-----------
flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8177: 0x01
cols@8178:    2

col    0[2] @8179: 1 
col    1[5] @8182: AAAAA

BBED> p ktbbhict
sb2 ktbbhict                                @36       2

8176-8076=76+(itc-1) * 24= 76+(2-1)* 24=100

2、我们对MSSM做测试

gyj@ZMDB> create tablespace mssm datafile '/u01/app/oracle/oradata/zmdb/mssm01.dbf' size 50M segment space management manual;

Tablespace created.


gyj@ZMDB> create table gyj_mssm(id int,name varchar2(100)) tablespace mssm;

Table created.

gyj@ZMDB> insert into gyj_mssm values(4,'DDDDD');

1 row created.

gyj@ZMDB> insert into gyj_mssm values(5,'EEEEE');

1 row created.

gyj@ZMDB> insert into gyj_mssm values(6,'FFFFF');

1 row created.

gyj@ZMDB> commit;

Commit complete.


gyj@ZMDB> col name for a20
gyj@ZMDB> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,id,name from gyj_mssm;

     FILE#     BLOCK#         ID NAME
---------- ---------- ---------- --------------------
        11        129          4 DDDDD
        11        129          5 EEEEE
        11        129          6 FFFFF

BBED> set file 11 block 129
        FILE#           11
        BLOCK#          129

BBED> p kdbr[0]
sb2 kdbr[0]                                 @110      8084

BBED> p *kdbr[0]
rowdata[24]
-----------
ub1 rowdata[24]                             @8176     0x2c

BBED> x /rnc
rowdata[24]                                 @8176    
-----------
flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8177: 0x01
cols@8178:    2

col    0[2] @8179: 4 
col    1[5] @8182: DDDDD

BBED> p ktbbhict
sb2 ktbbhict                                @36       2

8176-8084=68+(itc-1) * 24=68+(2-1)*24=92

3、为什么ASSM要比MSSM多了8个字节

************MSSM
BBED> set file 11 block 129
        FILE#           11
        BLOCK#          129

BBED> map /v
 File: /u01/app/oracle/oradata/zmdb/mssm01.dbf (11)
 Block: 129                                   Dba:0x02c00081
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       
    ub1 type_kcbh                           @0       
    ub1 frmt_kcbh                           @1       
    ub1 spare1_kcbh                         @2       
    ub1 spare2_kcbh                         @3       
    ub4 rdba_kcbh                           @4       
    ub4 bas_kcbh                            @8       
    ub2 wrp_kcbh                            @12      
    ub1 seq_kcbh                            @14      
    ub1 flg_kcbh                            @15      
    ub2 chkval_kcbh                         @16      
    ub2 spare3_kcbh                         @18      

 struct ktbbh, 72 bytes                     @20      
    ub1 ktbbhtyp                            @20      
    union ktbbhsid, 4 bytes                 @24      
    struct ktbbhcsc, 8 bytes                @28      
    sb2 ktbbhict                            @36      
    ub1 ktbbhflg                            @38      
    ub1 ktbbhfsl                            @39      
    ub4 ktbbhfnx                            @40      
    struct ktbbhitl[2], 48 bytes            @44      

 struct kdbh, 14 bytes                      @92      
    ub1 kdbhflag                            @92      
    sb1 kdbhntab                            @93      
    sb2 kdbhnrow                            @94      
    sb2 kdbhfrre                            @96      
    sb2 kdbhfsbo                            @98      
    sb2 kdbhfseo                            @100     
    sb2 kdbhavsp                            @102     
    sb2 kdbhtosp                            @104     

 struct kdbt[1], 4 bytes                    @106     
    sb2 kdbtoffs                            @106     
    sb2 kdbtnrow                            @108     

 sb2 kdbr[3]                                @110     

 ub1 freespace[8036]                        @116     

 ub1 rowdata[36]                            @8152    

 ub4 tailchk                                @8188

*****************ASSM
BBED> set file 10 block 135
        FILE#           10
        BLOCK#          135

File: /u01/app/oracle/oradata/zmdb/assm01.dbf (10)
 Block: 141                                   Dba:0x0280008d
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       
    ub1 type_kcbh                           @0       
    ub1 frmt_kcbh                           @1       
    ub1 spare1_kcbh                         @2       
    ub1 spare2_kcbh                         @3       
    ub4 rdba_kcbh                           @4       
    ub4 bas_kcbh                            @8       
    ub2 wrp_kcbh                            @12      
    ub1 seq_kcbh                            @14      
    ub1 flg_kcbh                            @15      
    ub2 chkval_kcbh                         @16      
    ub2 spare3_kcbh                         @18      

 struct ktbbh, 72 bytes                     @20      
    ub1 ktbbhtyp                            @20      
    union ktbbhsid, 4 bytes                 @24      
    struct ktbbhcsc, 8 bytes                @28      
    sb2 ktbbhict                            @36      
    ub1 ktbbhflg                            @38      
    ub1 ktbbhfsl                            @39      
    ub4 ktbbhfnx                            @40      
    struct ktbbhitl[2], 48 bytes            @44      

 struct kdbh, 14 bytes                      @100     
    ub1 kdbhflag                            @100     
    sb1 kdbhntab                            @101     
    sb2 kdbhnrow                            @102     
    sb2 kdbhfrre                            @104     
    sb2 kdbhfsbo                            @106     
    sb2 kdbhfseo                            @108     
    sb2 kdbhavsp                            @110     
    sb2 kdbhtosp                            @112     

 struct kdbt[1], 4 bytes                    @114     
    sb2 kdbtoffs                            @114     
    sb2 kdbtnrow                            @116     

 sb2 kdbr[3]                                @118     

 ub1 freespace[8028]                        @124     

 ub1 rowdata[36]                            @8152    

 ub4 tailchk                                @8188  

对比

struct kdbh, 14 bytes @92

---ASSM

struct kdbh, 14 bytes @100