数据块内部偏移量的基本计算方法
程序员文章站
2022-05-29 16:07:29
...
转载请注明出处: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