[20190120]行链接迁移与dml.txt

来源：这里教程网时间：2026-03-03 12:53:51 作者：

[20190120]行链接迁移与dml.txt --//昨天看https://jonathanlewis.wordpress.com/2019/01/18/dml-tablescans/的链接,里面提到DML Tablescans 问题, --//文章很难读懂,不过有一个问题至少我以前没注意,我一直以为如果修改出现行链接迁移的记录,一定会在对应块中使用 --//一个ITL槽.而且出现行迁移的记录一般会在对应块先建立dITL槽,我一直认为这个是事先预留,避免以后出现ITL槽不足的情况. --//链接:http://blog.itpub.net/267265/viewspace-2152498/=>[20180402]行链接行迁移与ITL槽6.txt --//另外作者在链接https://jonathanlewis.wordpress.com/2018/12/03/row-migration-2/提到另外一种情况parallel DML. --//链接http://blog.itpub.net/267265/viewspace-2152498/的一个例子测试. 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 table t1 ( n1 number, l1 varchar2(4000), s1 varchar2(200), l2 varchar2(4000), s2 varchar2(200), l3 varchar2(4000), s3 varchar2(200) ); --//L 表示 long长字段, S表示 short短字段. insert into t1 (n1,l1,s1) values(0,rpad('X',4000,'X'),rpad('X',200,'X')); commit; insert into t1 (n1,l1) values(1,null); commit; update t1 set l1 = rpad('A',4000), s1 = rpad('A',200), l2 = rpad('B',4000), s2 = rpad('B',200), l3 = rpad('C',4000), s3 = rpad('C',200) where n1 = 1 ; commit; execute dbms_stats.gather_table_stats(user,'t1'); update t1 set s1 = lower(s1), s2 = lower(s2), s3 = lower(s3) where n1 = 1 ; --//注:没提交. SCOTT@book> @ xid XIDUSN_XIDSLOT_XIDSQN ------------------------------ 8.26.1883 alter system flush buffer_cache; 3.分析对应块,通过bbed观察:: SCOTT@book> select rowid,n1 from t1 ; ROWID N1 ------------------ ---------- AAAWKiAAEAAABMkAAA 0 AAAWKiAAEAAABMkAAB 1 SCOTT@book> @ rowid AAAWKiAAEAAABMkAAB OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ---------------------------------------- 90786 4 4900 1 0x1001324 4,4900 alter system dump datafile 4 block 4900 --//首先通过bbed观察: BBED> set dba 4,4900 DBA 0x01001324 (16782116 4,4900) BBED> x /rncccccc dba 4,4900 *kdbr[1] rowdata[0] @3964 ---------- flag@3964: 0x20 (KDRHFH) lock@3965: 0x02 cols@3966: 0 nrid@3967:0x01001327.0 --//dba= 4,4900 ,仅仅包含nrid,指向下一个块nrid=0x01001327.0(dba=4,4903),flag=0x20.仅仅包含head标识.继续往下看: BBED> set dba 0x01001327 DBA 0x01001327 (16782119 4,4903) BBED> x /rncccccc dba 0x01001327 *kdbr[0] rowdata[0] @4167 ---------- flag@4167: 0x08 (KDRHFF) lock@4168: 0x01 cols@4169: 2 hrid@4170:0x01001324.1 nrid@4176:0x01001326.0 col 0[2] @4182: 1 col 1[4000] @4185: A --//dba = 4,4903. 包含2个字段n1,l1. 指向下一个块nrid=0x01001326.0(dba= 4,4902),前一个hrid=0x01001324.1(dba=4,4900). --//flag=0x01,仅仅包含First标识.继续往下看: BBED> set dba 0x01001326 DBA 0x01001326 (16782118 4,4902) BBED> x /rcccccc dba 0x01001326 *kdbr[0] rowdata[0] @3975 ---------- flag@3975: 0x00 (NONE) lock@3976: 0x02 cols@3977: 2 nrid@3978:0x01001325.0 col 0[200] @3984: a col 1[4000] @4185: B --//dba=4,4902. 包含2个字段s1,L2. 指向下一个块nrid=0x01001325.0(dba= 4,4901),奇怪没有hrid字段. --//flag=0x00. 没有任何信息.继续: BBED> set dba 0x01001325 DBA 0x01001325 (16782117 4,4901) BBED> x /rcccccc dba 0x01001325 *kdbr[0] rowdata[0] @3780 ---------- flag@3780: 0x04 (KDRHFL) lock@3781: 0x02 cols@3782: 3 col 0[200] @3783: b col 1[4000] @3984: C col 2[200] @7987: c --//dba=4,4901. 包含3个字段S2,L3,S3. 最后3个字段.flag=0x02,包含LAST标识. --//实际上还可以看出oracle是从最后字段向前组织数据的. 4.转储看看: SCOTT@book> @ xid XIDUSN_XIDSLOT_XIDSQN ------------------------------ 8.26.1883 alter system dump datafile 4 block 4900; alter system dump datafile 4 block 4903; alter system dump datafile 4 block 4902; alter system dump datafile 4 block 4901; --//dba = 4,4900 Block header dump: 0x01001324 Object id on Block? Y seg/obj: 0x162a2 csc: 0x03.177320e3 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1001320 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0008.014.0000075c 0x00c00123.03fa.07 C--- 0 scn 0x0003.17731d8b 0x02 0x0008.01a.0000075b 0x00c00123.03fa.2b ---- 1 fsc 0x0000.00000000 tab 0, row 1, @0xf18 tl: 9 fb: --H----- lb: 0x2 cc: 0 nrid: 0x01001327.0 --//0x1a=26,0x75b=1883 --//flag=--H-----. 使用ITL=0x2. xid=8.26.1883. --//dba = 4,4903 Block header dump: 0x01001327 Object id on Block? Y seg/obj: 0x162a2 csc: 0x03.17731d87 itc: 3 flg: E typ: 1 - DATA brn: 0 bdba: 0x1001320 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0008.014.0000075c 0x00c00123.03fa.06 --U- 1 fsc 0x0000.17731d8b 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x03 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000 ... tab 0, row 0, @0xfcb tl: 4021 fb: ----F--- lb: 0x1 cc: 2 hrid: 0x01001324.1 nrid: 0x01001326.0 col 0: [ 2] c1 02 col 1: [4000] 41 20 20 --//flag=----F---,使用ITL=0x1.注意看这个ITL槽指向的xid并不是0x0008.01a.0000075b(而是0x0008.014.0000075c). --//这个块仅仅包含N1,L1字段,并没有包含要修改S1,S2,S3字段. --//也就是dml并没有在该块修改ITL槽. --//dba = 4,4902 Block header dump: 0x01001326 Object id on Block? Y seg/obj: 0x162a2 csc: 0x03.177320e4 itc: 3 flg: E typ: 1 - DATA brn: 0 bdba: 0x1001320 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0008.014.0000075c 0x00c00123.03fa.05 C--- 0 scn 0x0003.17731d8b 0x02 0x0008.01a.0000075b 0x00c00123.03fa.2c ---- 1 fsc 0x0000.00000000 0x03 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000 ... block_row_dump: tab 0, row 0, @0xf0b tl: 4213 fb: -------- lb: 0x2 cc: 2 nrid: 0x01001325.0 col 0: [200] 61 20 --//flag=NONE.使用ITL=0x2,xid=0x0008.01a.0000075b --//dba = 4,4901 Block header dump: 0x01001325 Object id on Block? Y seg/obj: 0x162a2 csc: 0x03.177320e4 itc: 3 flg: E typ: 1 - DATA brn: 0 bdba: 0x1001320 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0008.014.0000075c 0x00c00123.03fa.04 C--- 0 scn 0x0003.17731d8b 0x02 0x0008.01a.0000075b 0x00c00123.03fa.2d ---- 1 fsc 0x0000.00000000 0x03 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000 tab 0, row 0, @0xe48 tl: 4408 fb: -----L-- lb: 0x2 cc: 3 col 0: [200] 62 20 20 20 20 --//flag=-----L--.使用ITL=0x2,xid=0x0008.01a.0000075b --//从以上分析可以看出如果dml修改在该块中没有对应记录,并不会使用ITL槽,这个是我以前没有注意的细节问题.