数据块内部偏移量的基本计算方法
程序员文章站
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
上一篇： Oracle 临时表空间问题小结
下一篇： MYSQL 这个多表查询语句该如何写