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

[20181226]简单探究cluster table.txt

程序员文章站 2022-07-04 11:00:31
[20181226]简单探究cluster table.txt--//简单探究cluster table.以前也做过,有点生疏了.1.环境:SCOTT@book> @ ver1PORT_STRING VERSION BANNER x86_64/Linux 2.4.xx 11.2.0.4.0 Orac ......

[20181226]简单探究cluster table.txt

--//简单探究cluster table.以前也做过,有点生疏了.

1.环境:
scott@book> @ ver1
port_string         version    banner
------------------- ---------- ----------------------------------------------------------------------------
x86_64/linux 2.4.xx 11.2.0.4.0 oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production

2.建立测试环境:
create cluster deptx_cluster (deptno number(2));

create table deptx
(
  deptno  number(2) ,
  dname   varchar2(14 byte),
  loc     varchar2(13 byte)
) cluster deptx_cluster (deptno);

alter table deptx add constraint pk_deptx primary key (deptno);  

create table empx
(
  empno     number(4) ,
  ename     varchar2(10 byte),
  job       varchar2(9 byte),
  mgr       number(4),
  hiredate  date,
  sal       number(7,2),
  comm      number(7,2),
  deptno    number(2) references deptx
) cluster deptx_cluster (deptno);

alter table empx  add constraint constraint_name primary key (empno);  

create index i_deptx_cluster_deptno on cluster deptx_cluster;
--//注这里不能使用unique,否则报ora-01715: unique may not be used with a cluster index

insert into deptx select * from dept where deptno=10;
insert into empx select * from emp where deptno=10;

insert into deptx select * from dept where deptno=20;
insert into empx select * from emp where deptno=20;

insert into deptx select * from dept where deptno=30;
insert into empx select * from emp where deptno=30;

insert into deptx select * from dept where deptno=40;
insert into empx select * from emp where deptno=40;

commit;

3.查看数据:
scott@book> select rowid,deptx.* from deptx;
rowid                  deptno dname          loc
------------------ ---------- -------------- -------------
aaawefaaeaaaairaaa         20 research       dallas
~~~~~~~~~~~~~~~~~~
aaawefaaeaaaaitaaa         30 sales          chicago
aaawefaaeaaaaiuaaa         40 operations     boston
aaawefaaeaaaaivaaa         10 accounting     new york

scott@book> select rowid,empx.* from empx where deptno=20;
rowid                   empno ename      job              mgr hiredate                   sal       comm     deptno
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
aaawefaaeaaaairaaa       7369 smith      clerk           7902 1980-12-17 00:00:00        800                    20
~~~~~~~~~~~~~~~~~~
aaawefaaeaaaairaab       7566 jones      manager         7839 1981-04-02 00:00:00       2975                    20
aaawefaaeaaaairaac       7788 scott      analyst         7566 1987-04-19 00:00:00       3000                    20
aaawefaaeaaaairaad       7876 adams      clerk           7788 1987-05-23 00:00:00       1100                    20
aaawefaaeaaaairaae       7902 ford       analyst         7566 1981-12-03 00:00:00       3000                    20

scott@book> @ dpc '' ''
plan_table_output
-------------------------------------
sql_id  fw7tmp1r74rf4, child number 0
-------------------------------------
select rowid,empx.* from empx where deptno=20
plan hash value: 1833007843
------------------------------------------------------------------------------------------------
| id  | operation            | name                   | e-rows |e-bytes| cost (%cpu)| e-time   |
------------------------------------------------------------------------------------------------
|   0 | select statement     |                        |        |       |     2 (100)|          |
|   1 |  table access cluster| empx                   |      5 |   190 |     2   (0)| 00:00:01 |
|*  2 |   index unique scan  | i_deptx_cluster_deptno |      1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
query block name / object alias (identified by operation id):
-------------------------------------------------------------
   1 - sel$1 / empx@sel$1
   2 - sel$1 / empx@sel$1
predicate information (identified by operation id):
---------------------------------------------------
   2 - access("deptno"=20)

--//可以发现查询empx表的deptno=20可以利用cluster table的索引.另外你可以注意一个特点cluster table里面的表rowid可以相同.比如下划线的内容.
--//实际上这样设计相关表的查询都保存在相同块中,连接访问会快许多.

scott@book> @ rowid aaawefaaeaaaairaaa
    object       file      block        row rowid_dba            dba                  text
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90399          4        555          0  0x100022b           4,555                alter system dump datafile 4 block 555 ;
--//转储dba=4,555看看.

3.转储分析:
scott@book> alter system flush buffer_cache;
system altered.

scott@book> alter system dump datafile 4 block 555 ;
system altered.

block header dump:  0x0100022b
 object id on block? y
 seg/obj: 0x1611f  csc: 0x03.175f42b6  itc: 2  flg: e  typ: 1 - data
     brn: 0  bdba: 0x1000228 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 itl           xid                  uba         flag  lck        scn/fsc
0x01   0x0009.01b.00000a44  0x00c00356.0394.31  c---    0  scn 0x0003.175f42b6
0x02   0x000a.001.00004cfa  0x00c02e94.0ef9.27  --u-    6  fsc 0x0000.175f42cc
bdba: 0x0100022b
data_block_dump,data header at 0x7f286b5f1064
===============
tsiz: 0x1f98
hsiz: 0x28
pbl: 0x7f286b5f1064
     76543210
flag=-------k
ntab=3
nrow=7
frre=-1
fsbo=0x28
fseo=0x1eb9
avsp=0x1e91
tosp=0x1e91
0xe:pti[0]  nrow=1  offs=0
0x12:pti[1] nrow=1  offs=1
0x16:pti[2] nrow=5  offs=2
--//说明有3个表,cluster表deptx_cluster以及表deptx,empx.行数分别是1,1,5(下面可以看到对于7条记录).后面offs表示偏移量.
0x1a:pri[0] offs=0x1f82
0x1c:pri[1] offs=0x1f6e
0x1e:pri[2] offs=0x1f4b
0x20:pri[3] offs=0x1f25
0x22:pri[4] offs=0x1f00
0x24:pri[5] offs=0x1edd
0x26:pri[6] offs=0x1eb9
block_row_dump:
tab 0, row 0, @0x1f82                               --//表0 cluster table deptx_cluster
tl: 22 fb: k-h-fl-- lb: 0x0  cc: 1
--//fb: k-h-fl--, k meaning cluster key.
--//the remaining rows have the second high order bit set (fb: -ch-fl--),
--//c meaning cluster table member.
curc: 6 comc: 6 pk: 0x0100022b.0 nk: 0x0100022b.0
--//curc: 6 current row count for this key in this block
--//comc: 6 committed row count for this key in this block
--//pk: pk: 0x0100022b.0 rowid of previous block for this cluster key
--//nk: 0x0100022b.0 rowid of next block for this cluster key
col  0: [ 2]  c1 15                                 --//数字20.
tab 1, row 0, @0x1f6e
tl: 20 fb: -ch-fl-- lb: 0x2  cc: 2 cki: 0           --//表1 deptx
                                                    --//c meaning cluster table member.
col  0: [ 8]  52 45 53 45 41 52 43 48               --//对应内容'research'
col  1: [ 6]  44 41 4c 4c 41 53                     --//对应内容'dallas'
tab 2, row 0, @0x1f4b                               --//表2 empx
tl: 35 fb: -ch-fl-- lb: 0x2  cc: 6 cki: 0
col  0: [ 3]  c2 4a 46
col  1: [ 5]  53 4d 49 54 48
col  2: [ 5]  43 4c 45 52 4b
col  3: [ 3]  c2 50 03
col  4: [ 7]  77 b4 0c 11 01 01 01
col  5: [ 2]  c2 09
tab 2, row 1, @0x1f25
tl: 38 fb: -ch-fl-- lb: 0x2  cc: 6 cki: 0
col  0: [ 3]  c2 4c 43
col  1: [ 5]  4a 4f 4e 45 53
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 04 02 01 01 01
col  5: [ 3]  c2 1e 4c
tab 2, row 2, @0x1f00
tl: 37 fb: -ch-fl-- lb: 0x2  cc: 6 cki: 0
col  0: [ 3]  c2 4e 59
col  1: [ 5]  53 43 4f 54 54
col  2: [ 7]  41 4e 41 4c 59 53 54
col  3: [ 3]  c2 4c 43
col  4: [ 7]  77 bb 04 13 01 01 01
col  5: [ 2]  c2 1f
tab 2, row 3, @0x1edd
tl: 35 fb: -ch-fl-- lb: 0x2  cc: 6 cki: 0
col  0: [ 3]  c2 4f 4d
col  1: [ 5]  41 44 41 4d 53
col  2: [ 5]  43 4c 45 52 4b
col  3: [ 3]  c2 4e 59
col  4: [ 7]  77 bb 05 17 01 01 01
col  5: [ 2]  c2 0c
tab 2, row 4, @0x1eb9
tl: 36 fb: -ch-fl-- lb: 0x2  cc: 6 cki: 0
col  0: [ 3]  c2 50 03
col  1: [ 4]  46 4f 52 44
col  2: [ 7]  41 4e 41 4c 59 53 54
col  3: [ 3]  c2 4c 43
col  4: [ 7]  77 b5 0c 03 01 01 01
col  5: [ 2]  c2 1f
end_of_block_dump
end dump data blocks tsn: 4 file#: 4 minblk 555 maxblk 555

--//通过bbed观察:
bbed> p dba 4,555 kdbt
struct kdbt[0], 4 bytes                     @114
   sb2 kdbtoffs                             @114      0
   sb2 kdbtnrow                             @116      1
struct kdbt[1], 4 bytes                     @118
   sb2 kdbtoffs                             @118      1
   sb2 kdbtnrow                             @120      1
struct kdbt[2], 4 bytes                     @122
   sb2 kdbtoffs                             @122      2
   sb2 kdbtnrow                             @124      5

--//与如下对应:
0xe:pti[0]  nrow=1  offs=0
0x12:pti[1] nrow=1  offs=1
0x16:pti[2] nrow=5  offs=2

--//比如可以看出对于当前块表empx位于*kdbr[2]开始.

bbed> x /rn *kdbr[0]
rowdata[201]                                @8166
------------
flag@8166: 0xac (kdrhfl, kdrhff, kdrhfh, kdrhfk)       --//cluster key.行头为ac 普通表为2c
lock@8167: 0x00
cols@8168:    1
kref@8169:    6
mref@8171:    6
hrid@8173:0x0100022b.0
nrid@8179:0x0100022b.0
col    0[2] @8185: 20

bbed> x /rcc *kdbr[1]
rowdata[181]                                @8146
------------
flag@8146: 0x6c (kdrhfl, kdrhff, kdrhfh, kdrhfc)      --//cluser table member .行头为6c 删除为7c,实际上加入kdrhfd标志.
lock@8147: 0x02
cols@8148:    2
col    0[8] @8150: research
col    1[6] @8159: dallas
--//注:不包括cluster table key.

bbed> x /rnccntnnn *kdbr[2]
rowdata[146]                                @8111
------------
flag@8111: 0x6c (kdrhfl, kdrhff, kdrhfh, kdrhfc)
lock@8112: 0x02
cols@8113:    6
col    0[3] @8115: 7369
col    1[5] @8119: smith
col    2[5] @8125: clerk
col    3[3] @8131: 7902
col    4[7] @8135: 1980-12-17 00:00:00
col    5[2] @8143: 800

4.做一个删除的手工恢复看看.

scott@book> delete from empx where empno=7566;
1 row deleted.

scott@book> commit ;
commit complete.

scott@book> alter system flush buffer_cache;
system altered.

scott@book> alter system dump datafile 4 block 555 ;
system altered.

--//仅仅贴出改动部分:
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: k-h-fl-- lb: 0x0  cc: 1
curc: 6 comc: 5 pk: 0x0100022b.0 nk: 0x0100022b.0
col  0: [ 2]  c1 15
--//comc 5 少1条记录.

...

tl: 35 fb: -ch-fl-- lb: 0x0  cc: 6 cki: 0
col  0: [ 3]  c2 4a 46
col  1: [ 5]  53 4d 49 54 48
col  2: [ 5]  43 4c 45 52 4b
col  3: [ 3]  c2 50 03
col  4: [ 7]  77 b4 0c 11 01 01 01
col  5: [ 2]  c2 09
tab 2, row 1, @0x1f25
tl: 4 fb: -chdfl-- lb: 0x2  cc: 0 cki: 0  --//加入d标志.
tab 2, row 2, @0x1f00

--//如果手工修复,需要修改2处.

bbed> x /rn *kdbr[0]
rowdata[201]                                @8166
------------
flag@8166: 0xac (kdrhfl, kdrhff, kdrhfh, kdrhfk)
lock@8167: 0x00
cols@8168:    1
kref@8169:    6
mref@8171:    5
~~~~~~~~~~~~~~~
hrid@8173:0x0100022b.0
nrid@8179:0x0100022b.0
col    0[2] @8185: 20

bbed> x /rnccntnnn *kdbr[3]
rowdata[108]                                @8073
------------
flag@8073: 0x7c (kdrhfl, kdrhff, kdrhfd, kdrhfh, kdrhfc)
lock@8074: 0x02
cols@8075:    0

--//执行如下:
assign /d offset 8171=6;
assign /x offset 8073=6c;

bbed> assign /d offset 8171=6;
warning: contents of previous bifile will be lost. proceed? (y/n) y
ub1 rowdata[0]                              @8171     0x06

bbed> assign /x offset 8073=6c;
ub1 rowdata[0]                              @8073     0x6c

--//检查:
bbed> x /rn *kdbr[0]
rowdata[201]                                @8166
------------
flag@8166: 0xac (kdrhfl, kdrhff, kdrhfh, kdrhfk)
lock@8167: 0x00
cols@8168:    1
kref@8169:    6
mref@8171:    6
hrid@8173:0x0100022b.0
nrid@8179:0x0100022b.0
col    0[2] @8185: 20

bbed> x /rnccntnnn *kdbr[2]
rowdata[146]                                @8111
------------
flag@8111: 0x6c (kdrhfl, kdrhff, kdrhfh, kdrhfc)
lock@8112: 0x00
cols@8113:    6

col    0[3] @8115: 7369
col    1[5] @8119: smith
col    2[5] @8125: clerk
col    3[3] @8131: 7902
col    4[7] @8135: 1980-12-17 00:00:00
col    5[2] @8143: 800

bbed> sum apply
check value for file 4, block 555:
current = 0xf209, required = 0xf209

bbed> verify
dbverify - verification starting
file = /mnt/ramdisk/book/users01.dbf
block = 555

block checking: dba = 16777771, block type = ktb-managed data block
data header at 0x7fd6f1fd9264
kdbchk: the amount of space used is not equal to block size
        used=263 fsc=34 avsp=7825 dtl=8088
block 555 failed with check code 6110
--//空间问题暂时不理会.
--//验证修改是否有效.
scott@book> select rowid,empx.* from empx where deptno=20;
rowid                   empno ename      job              mgr hiredate                   sal       comm     deptno
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
aaawefaaeaaaairaaa       7369 smith      clerk           7902 1980-12-17 00:00:00        800                    20
aaawefaaeaaaairaab       7566 jones      manager         7839 1981-04-02 00:00:00       2975                    20
aaawefaaeaaaairaac       7788 scott      analyst         7566 1987-04-19 00:00:00       3000                    20
aaawefaaeaaaairaad       7876 adams      clerk           7788 1987-05-23 00:00:00       1100                    20
aaawefaaeaaaairaae       7902 ford       analyst         7566 1981-12-03 00:00:00       3000                    20
--//empno=7566可以查询到.如果通过索引是无法查询到信息.
scott@book> select rowid,empx.* from empx where empno=7566;
no rows selected

--//如果不修复comc的偏移,还原:
bbed> assign /d offset 8171=5;
ub1 rowdata[0]                              @8171     0x05

bbed> sum apply
check value for file 4, block 555:
current = 0xf109, required = 0xf109

bbed> verify
dbverify - verification starting
file = /mnt/ramdisk/book/users01.dbf
block = 555

block checking: dba = 16777771, block type = ktb-managed data block
data header at 0x105cc64
kdbchk:  key comref count wrong
         keyslot=0
block 555 failed with check code 6121

--//会报如上错误.不过查询没有问题.

scott@book> alter system flush buffer_cache;
system altered.

scott@book> select rowid,empx.* from empx where deptno=20;
rowid                   empno ename      job              mgr hiredate                   sal       comm     deptno
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
aaawefaaeaaaairaaa       7369 smith      clerk           7902 1980-12-17 00:00:00        800                    20
aaawefaaeaaaairaab       7566 jones      manager         7839 1981-04-02 00:00:00       2975                    20
aaawefaaeaaaairaac       7788 scott      analyst         7566 1987-04-19 00:00:00       3000                    20
aaawefaaeaaaairaad       7876 adams      clerk           7788 1987-05-23 00:00:00       1100                    20
aaawefaaeaaaairaae       7902 ford       analyst         7566 1981-12-03 00:00:00       3000                    20

5.最后修复bbed verify的错误.
--//这个错误我建议不熟悉bbed,不再修复,比较繁琐.

bbed> assign /d offset 8171=6;
ub1 rowdata[0]                              @8171     0x06

bbed> sum apply
check value for file 4, block 555:
current = 0xf209, required = 0xf209

bbed> verify
dbverify - verification starting
file = /mnt/ramdisk/book/users01.dbf
block = 555

block checking: dba = 16777771, block type = ktb-managed data block
data header at 0x105cc64
kdbchk: the amount of space used is not equal to block size
        used=263 fsc=34 avsp=7825 dtl=8088
block 555 failed with check code 6110

--//理论used+fsc+avsp=dtl
--//dtl-used-fsc= 8088-263-34 = 7791
--//然后assign kdbh.kdbhavsp=7791,如果修改事务itl槽信息,步骤也不少.

bbed> assign kdbh.kdbhavsp=7791
sb2 kdbhavsp                                @110      7791

bbed> sum apply
check value for file 4, block 555:
current = 0xf2f7, required = 0xf2f7

bbed> verify
dbverify - verification starting
file = /mnt/ramdisk/book/users01.dbf
block = 555

block checking: dba = 16777771, block type = ktb-managed data block
data header at 0x107ec64
kdbchk: space available on commit is incorrect
        tosp=7863 fsc=34 stb=0 avsp=7791
block 555 failed with check code 6111

--//tosp - fsc - stb = avsp.
--//avsp+fsstb=  7791+34+0 = 7825.

bbed> assign kdbh.kdbhtosp=7825
sb2 kdbhtosp                                @112      7825

bbed> sum apply
check value for file 4, block 555:
current = 0xf2d1, required = 0xf2d1

bbed> verify
dbverify - verification starting
file = /mnt/ramdisk/book/users01.dbf
block = 555
dbverify - verification complete
total blocks examined         : 1
total blocks processed (data) : 1
total blocks failing   (data) : 0
total blocks processed (index): 0
total blocks failing   (index): 0
total blocks empty            : 0
total blocks marked corrupt   : 0
total blocks influx           : 0
message 531 not found;  product=rdbms; facility=bbed

6.修复索引:
scott@book> alter index pk_empx rebuild online;
index altered.
--//注意一定要加online,不然不回表,无法修复错误索引错误.

scott@book> select rowid,empx.* from empx where empno=7566;
rowid                   empno ename      job              mgr hiredate                   sal       comm     deptno
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
aaawefaaeaaaairaab       7566 jones      manager         7839 1981-04-02 00:00:00       2975                    20

总结:
--//修复cluster table要比普通表有难度.