[20180402]行链接行迁移与ITL槽6.txt
--//上个星期测试行迁移与ITL浪费的问题,链接http://blog.itpub.net/267265/viewspace-2152265/
--//当时遇到一个问题,就是oracle为什么每次发生行迁移要建立一个ITL槽.
--//在别人的指点下,终于明白为什么,实际上的目的是为了避免ITL不足.
--//以前测试的链接:
[20160726]行链接行迁移与ITL槽.txt
[20160727]行链接行迁移与ITL槽2.txt
[20160728]行链接行迁移与ITL槽3.txt
[20160728]行链接行迁移与ITL槽4.txt
[20160729]行链接行迁移与ITL槽4.txt
http://blog.itpub.net/267265/viewspace-2122700/
http://blog.itpub.net/267265/viewspace-2122663/
http://blog.itpub.net/267265/viewspace-2122599/
http://blog.itpub.net/267265/viewspace-2122712/
--//还是通过例子说明问题.
1.环境:
SCOTT@book> @ &r/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
create table t (id number,pad varchar2(200));
alter table t pctfree 0;
create unique index pk_t on t(id);
alter table t add constraint pk_t primary key (id) enable validate;
insert into t(id) select rownum from dual connect by level<=734;
commit ;
SCOTT@book> select rowid,id from t where id =1 or id=734 or id=733;
ROWID ID
------------------ ----------
AAAWEKAAEAAAAImAAA 1
AAAWEKAAEAAAAImALc 733
AAAWEKAAEAAAAInAAA 734
--// 仅仅id=734在另外的块中。
2.测试:
SCOTT@book> update t set pad=lpad('x',20,'x') ;
734 rows updated.
--//注意不要提交.
SCOTT@book> @ &r/rowid AAAWEKAAEAAAAImAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90378 4 550 0 0x1000226 4,550 alter system dump datafile 4 block 550 ;
SYS@book> alter system checkpoint ;
System altered.
SYS@book> alter system checkpoint ;
System altered.
--//保证写盘.
SYS@book> alter system dump datafile 4 block 550 ;
System altered.
$ grep nrid /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_407.trc | cut -d":" -f2 | cut -d"." -f1 | sort | uniq -c
134 0x01000223
134 0x01000224
134 0x01000225
135 0x01000227
134 0x01000234
62 0x01000235
--//136+134+134+134+134+61=733,有733行出现了迁移,有一点极端.^_^.全部出现了行迁移。
--//顺便找1个dba=0x01000223看看.最好退出会话在进入,生成新的跟踪文件:
SYS@book> @ &r/dfb16 0x01000223
RFILE# BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
4 547 alter system dump datafile 4 block 547 ;
SYS@book> alter system dump datafile 4 block 547 ;
System altered.
$ sed -n "/Itl/,/bdba:/p" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_441.trc
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.001.00004ccc 0x00c036f0.0ef8.09 ---- 134 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x04 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x05 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x06 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x07 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x08 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x09 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x0a 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x0b 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x0c 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x0d 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x0e 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x0f 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x10 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x11 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x12 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x13 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x14 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x15 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x16 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x17 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x18 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x19 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x1a 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x1b 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x1c 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x1d 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x1e 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x1f 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x20 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x21 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x22 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x23 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x24 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x25 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x26 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x27 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x28 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x29 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x2a 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x2b 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x2c 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x2d 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x2e 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x2f 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x30 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x31 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x32 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x33 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x34 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x35 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x36 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x37 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x38 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x39 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x3a 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x3b 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
...
0x7c 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x7d 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x7e 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x7f 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x80 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x81 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x82 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x83 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x84 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x85 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x86 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x87 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
0x88 0x0000.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00000000
bdba: 0x01000223
--//你可以发现Flag=C---,也就是已经提交.而xid=0x0000.000.00000000,Uba=0x00000000.0000.00,也就是这些ITL根本没有事务.
--//共0x88=136槽,减去前面2个,正好134个(LCK=134也说明问题).也就是每次迁移都要建立1个ITL槽.
--//而真正发生事务的ITL是:
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.001.00004ccc 0x00c036f0.0ef8.09 ---- 134 fsc 0x0000.00000000
SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.1.19660
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- -----
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 1 19660; 10 1 19660 3 14083 3832 23 ACTIVE 31 2200 0A000100CC4C0000 0000000081B92928 2018-04-02 15:39:49 7683
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 14083;
--//19660=0x4ccc.
--//0x0ef8=3832,正好对上.
SYS@book> alter system dump datafile 4 block 550 ;
System altered.
Block header dump: 0x01000226
Object id on Block? Y
seg/obj: 0x1610a csc: 0x03.175e47ea itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000220 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.01e.00004cc8 0x00c036e1.0ef8.0c C--- 0 scn 0x0003.175e47bc
0x02 0x000a.001.00004ccc 0x00c03703.0ef8.16 ---- 733 fsc 0x0000.00000000
bdba: 0x01000226
SYS@book> @ &r/dfb16 0x00c03703
RFILE# BLOCK# TEXT
------------ ------------ ------------------------------------------------------------
3 14083 alter system dump datafile 3 block 14083 ;
--//生成这些ITL的目的就是当如果迁移行发生后如果有dml事务,就必须在对应行上tx锁.这样在当前块与迁移块上都必须使用ITL槽,如果该块包含许多迁移行,
--//在大量dml事务时使用ITL,而行迁移前先建立了ITL槽,就一定程度避免ITL槽的不足.而导致的事务停顿,甚至无法运行的情况.
3.继续测试:
--//先提交事务:
SCOTT@book> commit ;
Commit complete.
SCOTT@book> update t set pad=lpad('y',20,'y') where id=1;
1 row updated.
--//注意不要提交.
SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.29.19594
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ----------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 29 19594; 10 29 19594 3 14123 3832 33 ACTIVE 1 2 0A001D008A4C0000 0000000081B92928 2018-04-02 16:01:40 7683
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 14123;
SYS@book> alter system checkpoint ;
System altered.
SYS@book> alter system checkpoint ;
System altered.
SYS@book> alter system checkpoint ;
System altered.
SYS@book> alter system dump datafile 4 block 550 ;
System altered.
Block header dump: 0x01000226
Object id on Block? Y
seg/obj: 0x1610a csc: 0x03.175e4f72 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000220 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.01d.00004c8a 0x00c0372b.0ef8.20 ---- 1 fsc 0x0000.00000000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x02 0x000a.001.00004ccc 0x00c03703.0ef8.16 C--- 0 scn 0x0003.175e4dd2
bdba: 0x01000226
--//19594=0x4c8a
block_row_dump:
tab 0, row 0, @0x1f8f
tl: 9 fb: --H----- lb: 0x1 cc: 0
nrid: 0x01000227.1
--//nrid =0x01000227.1
SYS@book> @ &r/dfb16 0x01000227
RFILE# BLOCK# TEXT
------------ ------------ ------------------------------------------------------------
4 551 alter system dump datafile 4 block 551 ;
Block header dump: 0x01000227
Object id on Block? Y
seg/obj: 0x1610a csc: 0x03.175e4f72 itc: 137 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000220 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.01e.00004cc8 0x00c036e1.0ef8.0d C--- 0 scn 0x0003.175e47bc
0x02 0x000a.001.00004ccc 0x00c03703.0ef8.17 C--- 0 scn 0x0003.175e4dd2
0x03 0x000a.01d.00004c8a 0x00c0372b.0ef8.21 ---- 1 fsc 0x0000.00000000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
block_row_dump:
tab 0, row 0, @0x143
tl: 28 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 3] c2 08 23
col 1: [20] 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
tab 0, row 1, @0x12c8
tl: 33 fb: ----FL-- lb: 0x3 cc: 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
hrid: 0x01000226.0
col 0: [ 2] c1 02
col 1: [20] 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
--//可以发现在迁移块上也占用一个ITL槽.
--//可以发现在行迁移后在迁移块中ITL,就是避免以后的事务中ITL槽的不足.
61
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
