分享程序员开发的那些事...
更新时间：2022-09-06 23:40:56
[20160528]快速提交的一个疑问2.txt
--链接 http://blog.itpub.net/267265/viewspace-2108017/
--在上一次链接里面提到在快速提交时,itl槽的_ktbitun._ktbitfsc记录是dml记录长度减少的长度.
--如果清除后,会kdbh.kdbhavsp相加写回kdbh.kdbhavsp,这样就很容易联想到另外的问题,如果修改记录长度增加呢?
--会出现什么情况呢?还是通过例子来说明:
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
--家里只有12c,而且还是windows的版本.
create table t ( id number ,text varchar2(200));
insert into t select rownum ,lpad('a',50,'a') from dual connect by level<=4;
commit ;
alter system checkpoint ;
SCOTT@test01p> column text noprint
SCOTT@test01p> select ora_rowscn ,rowid ,t.* from t;
ORA_ROWSCN ROWID ID
---------- ------------------ ----------
22619635 AAAZo6AAJAAAACNAAA 1
22619635 AAAZo6AAJAAAACNAAB 2
22619635 AAAZo6AAJAAAACNAAC 3
22619635 AAAZo6AAJAAAACNAAD 4
SCOTT@test01p> @ rowid AAAZo6AAJAAAACNAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
105018 9 141 0 9,141 alter system dump datafile 9 block 141 ;
2.使用bbed观察,注意在windows下使用bbed,输入block参数要在原来基础上加+1(别问我为什么?),另外如果改变了块内容,***退出bbed
再进入,不然可能看到信息不正确.
BBED> set dba 9,142
DBA 0x0240008e (37748878 9,142)
BBED> p kdbr
sb2 kdbr[0] @118 7860
sb2 kdbr[1] @120 7917
sb2 kdbr[2] @122 7974
sb2 kdbr[3] @124 8031
BBED> p kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
b1 kdbhntab @101 1
b2 kdbhnrow @102 4
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 26
sb2 kdbhfseo @108 7860
b2 kdbhavsp @110 7834
b2 kdbhtosp @112 7834
--//修改kdbhavsp=7833,减少1,这样做的目的为了下面执行verify时出错,了解used,fsc,avsp,dtl的数值.
BBED> assign kdbhavsp=7833
b2 kdbhavsp @110 7833
BBED> sum apply
Check value for File 9, Block 142:
current = 0x07f8, required = 0x07f8
BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF
BLOCK = 141
Block Checking: DBA = 37748877, Block Type = KTB-managed data block
data header at 0x960264
kdbchk: the amount of space used is not equal to block size
used=254 fsc=0 avsp=7833 dtl=8088
Block 141 failed with check code 6110
--//还原kdbhavsp=7834
BBED> assign kdbhavsp=7834
b2 kdbhavsp @110 7834
3.修改记录增加长度看看.
update t set text=lpad('A',65,'A') where id=1;
commit ;
alter system checkpoint ;
SCOTT@test01p> select ora_rowscn ,rowid ,t.* from t;
ORA_ROWSCN ROWID ID
---------- ------------------ ----------
22620572 AAAZo6AAJAAAACNAAA 1
22620572 AAAZo6AAJAAAACNAAB 2
22620572 AAAZo6AAJAAAACNAAC 3
22620572 AAAZo6AAJAAAACNAAD 4
SCOTT@test01p> @ 10to16 22620572
10 to 16 HEX REVERSE16
-------------- ------------------
000000159299c 0x9c295901
--//通过bbed观察:
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @7888 0x2c
BBED> x /rnc
rowdata[0] @7888
----------
flag@7888: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7889: 0x02
cols@7890: 2
col 0[2] @7891: 1
col 1[65] @7894: AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
--//可以发现使用ITL槽0x02.
BBED> p kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
b1 kdbhntab @101 1
b2 kdbhnrow @102 4
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 26
sb2 kdbhfseo @108 7788
b2 kdbhavsp @110 7819
b2 kdbhtosp @112 7819
BBED> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000c
ub2 kxidslt @70 0x001f
ub4 kxidsqn @72 0x00000a18
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x01401362
ub2 kubaseq @80 0x02ae
ub1 kubarec @82 0x2c
ub2 ktbitflg @84 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @86
b2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x0159299c
--// ktbbh.ktbbhitl[1].ktbitbas=0x0159299c,与上面看到ora_rowscn一致.
--// ktbbh.ktbbhitl[1]._ktbitfsc = 0
--//注意 kdbhavsp 从 7834 变成 了 7819,正好减少了15字节.也就是如果DML后长度增加
--//对应的事务槽_ktbitfsc = 0 ,而kdbh.kdbhavsp,kdbh.kdbhtosp减少相应长度.
BBED> assign kdbhavsp=7818
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
b2 kdbhavsp @110 7818
BBED> sum apply
Check value for File 9, Block 142:
current = 0x0db9, required = 0x0db9
BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\SAMPLE_SCHEMA_USERS01.DBF
BLOCK = 141
Block Checking: DBA = 37748877, Block Type = KTB-managed data block
data header at 0x2210264
kdbchk: the amount of space used is not equal to block size
used=269 fsc=0 avsp=7818 dtl=8088
Block 141 failed with check code 6110
--//可以看到used 从254=>269 ,增加15字节.
--//从以上测试可以发生如果dml长度增加,对应的itl事务槽的_ktbitun._ktbitfsc=0.
--//还原.
BBED> assign kdbhavsp=7819
b2 kdbhavsp @110 7819
4.继续探究:
SCOTT@test01p> alter system dump datafile 9 block 141 ;
System altered.
Block header dump: 0x0240008d
Object id on Block? Y
seg/obj: 0x19a3a csc: 0x00.159299a itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2400088 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0010.01b.000001f1 0x01400907.00ab.0d C--- 0 scn 0x0000.015925f3
0x02 0x000c.01f.00000a18 0x01401362.02ae.2c --U- 1 fsc 0x0000.0159299c
--//可以发现上面的事务使用itl槽0x02 ,Flag=--U-,lck=1,还没有清除.
--如果我分别执行2个事务覆盖相应的事务槽会发生什么情况呢?(注意3次对应不同的记录)
update t set id=3 where id=3;
commit
update t set id=4 where id=4;
commit ;
update t set id=2 where id=2;
commit;
alter system checkpoint ;
alter system dump datafile 9 block 141 ;
--//检查转储文件:
Block header dump: 0x0240008d
Object id on Block? Y
seg/obj: 0x19a3a csc: 0x00.1593286 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2400088 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000e.01d.00000810 0x0140186d.02d8.0a --U- 1 fsc 0x0000.015932ef
0x02 0x0002.012.00005737 0x0140236f.04b1.25 --U- 1 fsc 0x0000.01593288
--//可以发现原来0x02的信息被覆盖,注意这个时候的提交标志是flag=--U-,也就是还是使用快速提交.
BBED> p *kdbr[1]
rowdata[129]
------------
ub1 rowdata[129] @8017 0x2c
BBED> x /rnc
rowdata[129] @8017
------------
flag@8017: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8018: 0x01
~~~~~~~~~~~~~~~~~
cols@8019: 2
col 0[2] @8020: 2
col 1[50] @8023: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
BBED> p *kdbr[2]
rowdata[186]
------------
ub1 rowdata[186] @8074 0x2c
BBED> x /rnc
rowdata[186] @8074
------------
flag@8074: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8075: 0x00
cols@8076: 2
col 0[2] @8077: 3
col 1[50] @8080: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
BBED> p *kdbr[3]
rowdata[243]
------------
ub1 rowdata[243] @8131 0x2c
BBED> x /rnc
rowdata[243] @8131
------------
flag@8131: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8132: 0x02
~~~~~~~~~~~~~~~
cols@8133: 2
col 0[2] @8134: 4
col 1[50] @8137: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
--//id=2,4 的lock没有清除.如果这个时候执行
update t set id=2 where id=2;
commit;
alter system checkpoint ;
alter system dump datafile 9 block 141 ;
--//会使用itl 0x02槽,这样itl 0x01槽会被清除,因为itl槽 0x01对应的记录是id=1.
Block header dump: 0x0240008d
Object id on Block? Y
seg/obj: 0x19a3b csc: 0x00.15935a9 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2400088 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000e.01d.00000810 0x0140186d.02d8.0a C--- 0 scn 0x0000.015932ef
0x02 0x0004.018.000057a0 0x01403238.054f.16 --U- 1 fsc 0x0000.015935ab
总结:
总之如果dml记录长度增加对应的itl槽_ktbitun._ktbitfsc=0.而kdbh.kdbhavsp,kdbh.kdbhtosp减少相应长度.