本文共 8797 字，大约阅读时间需要 29 分钟。
[20160803]另类行迁移.txt
--前几天做测试时,链接:
>[20160729]行链接行迁移与ITL槽4.txt--发现一个块中的记录在字段长度变长后全部发生行迁移,感觉很奇怪,当时也没有仔细思考(开始以为至少有一些记录不会发生行迁移的
--情况),事后才想起来以前我做过类似的测试,参考链接: > [20150720]为什么8K数据块Hakan Factor=736--为了加强记忆,重复测试:
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 Productioncreate 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 ------------------ ---------- AAAXWyAAEAAAALrAAA 1 AAAXWyAAEAAAALrALc 733 AAAXWyAAEAAAALsAAA 734SCOTT@book> @ &r/rowid AAAXWyAAEAAAALrAAA
OBJECT FILE BLOCK ROW DBA TEXT ---------- ---------- ---------- ---------- -------------------- ---------------------------------------- 95666 4 747 0 4,747 alter system dump datafile 4 block 747 ; --//dba=4,747,一块共733条记录。SCOTT@book> alter system checkpoint ;
System altered.--按照前面的理论,一条记录至少保留9个字节.
BBED> set dba 4,747 DBA 0x0240008e (37748878 9,142)BBED> map
File: /mnt/ramdisk/book/users01.dbf (4) Block: 747 Dba:0x010002eb ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 72 bytes @20 struct kdbh, 14 bytes @100 struct kdbt[1], 4 bytes @114 sb2 kdbr[733] @118 ub1 freespace[1579] @1584 ub1 rowdata[5025] @3163 ub4 tailchk @8188-- 计算如下：
8192-1584-4=6604 (扣除尾部tailchk4个字节) 6604/733=9.00954979536152796725 (平均9个字节,如果全部记录发生行迁移，至少要保存9个字节) 6604-733*9=7 (余数)BBED> x /rnc *kdbr[0]
rowdata[3341] @6504 ------------- flag@6504: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@6505: 0x01 cols@6506: 1 col 0[2] @6507: 1BBED> x /rnc *kdbr[1]
rowdata[3347] @6510 ------------- flag@6510: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@6511: 0x01 cols@6512: 1 col 0[2] @6513: 2--//这里1条记录占6个字节,数字1占2个字节.还剩下9-6=3个字节,其中一个作为长度指示器,这样修改字段pad字符长度增
--//加3，应该就会出现行迁移的情况.测试看看.2.测试:
$ cat a.sql update t set pad=lpad('x',3,'x') where id=&&1; commit ; quit;spool b.sh
select 'sqlplus -s scott/book @a.sql '|| rownum from dual connect by level<=734; spool off--//编辑整理,执行b.sh.这样将产生大量的行迁移.
SCOTT@book> alter system checkpoint ;
System altered.SCOTT@book> alter system dump datafile 4 block 747 ;
System altered.$ grep nrid /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_39869.trc | cut -d":" -f2 | cut -d"." -f1 | sort | uniq -c
167 0x010002ec 167 0x010002ed 167 0x010002ee 167 0x010002ef 61 0x010002f9167+167+167+167+61=729,仅仅4条没有发生行迁移.
--转储其中1块0x010002ec
SCOTT@book> @ &r/dfb16 0x010002ec RFILE# BLOCK# TEXT ---------- ---------- ------------------------------------------------------------ 4 748 alter system dump datafile 4 block 748 ;SCOTT@book> alter system checkpoint ;
System altered.SCOTT@book> alter system dump datafile 4 block 748 ;
System altered.$ sed -n "/Itl/,/bdba:/p" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_41566.trc
Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.008.000036c8 0x00c00103.091d.0b C--- 0 scn 0x0003.157b2d98 0x02 0x000a.00c.0000d54b 0x00c002dd.2b3c.17 C--- 0 scn 0x0003.157b37e2 0x03 0x000a.005.0000d554 0x00c002dd.2b3c.1a C--- 0 scn 0x0003.157b37e7 0x04 0x000a.007.0000d54e 0x00c002dd.2b3c.1d C--- 0 scn 0x0003.157b37ec 0x05 0x000a.01f.0000d554 0x00c002dd.2b3c.20 C--- 0 scn 0x0003.157b37f1 0x06 0x000a.017.0000d549 0x00c002dd.2b3c.23 C--- 0 scn 0x0003.157b37f6 0x07 0x000a.00a.0000d565 0x00c002dd.2b3c.26 C--- 0 scn 0x0003.157b37fb 0x08 0x000a.01c.0000d51e 0x00c002dd.2b3c.29 C--- 0 scn 0x0003.157b3800 0x09 0x000a.018.0000d55b 0x00c002dd.2b3c.2c C--- 0 scn 0x0003.157b3805 0x0a 0x000a.01a.0000d553 0x00c002dd.2b3c.2f C--- 0 scn 0x0003.157b380a 0x0b 0x000a.010.0000d549 0x00c002dd.2b3c.32 C--- 0 scn 0x0003.157b380f ... 0xa5 0x000a.016.0000d559 0x00c002e3.2b3c.1a C--- 0 scn 0x0003.157b3b19 0xa6 0x0009.004.000036c8 0x00c00106.091d.10 C--- 0 scn 0x0003.157b3b1f 0xa7 0x0007.010.000018cb 0x00c000e3.0661.21 C--- 0 scn 0x0003.157b3b24 0xa8 0x000a.019.0000d555 0x00c002e3.2b3c.1d C--- 0 scn 0x0003.157b3b29 0xa9 0x0003.017.000010ed 0x00c0015d.0cda.0f --U- 1 fsc 0x0000.157b3c6a bdba: 0x010002ec-- 0xa9 = 169. 总共占用169个ITL槽.这也进一步证明8k的数据库最多占用169个ITL槽.
BBED> set dba 4,748
DBA 0x010002ec (16777964 4,748)BBED> map /v
File: /mnt/ramdisk/book/users01.dbf (4) Block: 748 Dba:0x010002ec ------------------------------------------------------------ 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 @18struct ktbbh, 4080 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[169], 4056 bytes @44struct kdbh, 14 bytes @4108
ub1 kdbhflag @4108 sb1 kdbhntab @4109 sb2 kdbhnrow @4110 sb2 kdbhfrre @4112 sb2 kdbhfsbo @4114 sb2 kdbhfseo @4116 sb2 kdbhavsp @4118 sb2 kdbhtosp @4120struct kdbt[1], 4 bytes @4122
sb2 kdbtoffs @4122 sb2 kdbtnrow @4124sb2 kdbr[168] @4126
ub1 freespace[870] @4462
ub1 rowdata[2856] @5332
ub4 tailchk @8188
--可以发现freespace还有870,但是ITL槽的数量不在增加.
SCOTT@book> analyze table t list chained rows;
Table analyzed.SCOTT@book> analyze table t compute statistics;
Table analyzed.SCOTT@book> select table_name, num_rows, chain_cnt, avg_row_len from user_tables where table_name='T';
TABLE_NAME NUM_ROWS CHAIN_CNT AVG_ROW_LEN ---------- ---------- ---------- ----------- T 734 729 173.继续演示ITL不足的情况,如果有多个事务出现在该块,由于要增加ITL槽,这样必须抢占freespace,这样就不能保证记录的扩展,实际上
oracle这个时候不允许增加itl槽,这样就出现itl不足的情况.这也是我前次测试的结果：--重来。
--drop table t purge ; 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 ------------------ ---------- AAAXW0AAEAAAALrAAA 1 AAAXW0AAEAAAALrALc 733 AAAXW0AAEAAAALsAAA 734SCOTT@book> @ &r/rowid AAAXW0AAEAAAALrAAA
OBJECT FILE BLOCK ROW DBA TEXT ---------- ---------- ---------- ---------- -------------------- ---------------------------------------- 95668 4 747 0 4,747 alter system dump datafile 4 block 747 ; BBED> map File: /mnt/ramdisk/book/users01.dbf (4) Block: 747 Dba:0x010002eb ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 72 bytes @20 struct kdbh, 14 bytes @100 struct kdbt[1], 4 bytes @114 sb2 kdbr[733] @118 ub1 freespace[1579] @1584 ub1 rowdata[5025] @3163 ub4 tailchk @8188--//可以发现freespace=1579 ,还有许多。
--session 1,注意不提交:
SCOTT@book(68,1377)> update t set pad=lpad('a',10,'a') where id=1; 1 row updated.--session 2,注意不提交:
SCOTT@book(101,3247)> update t set pad=lpad('a',10,'a') where id=2; 1 row updated.--session 3,注意不提交:
SCOTT@book(112,565)> update t set pad=lpad('a',10,'a') where id=3;--//看看等待事件：
cat wait.sql
select p1raw,p2raw,p3raw,p1,p2,p3,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class<>'Idle' order by event ;SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- --------------- 0000000062657100 0000000000000001 00 1650815232 1 0 123 265 25 SQL*Net message to client WAITED SHORT TIME 2 0 0000000054580004 0000000000090005 00000000000036C9 1415053316 589829 14025 112 565 38 enq: TX - allocate ITL entry WAITING 8500417 9--出现了enq: TX - allocate ITL entry等待事件，而这个时候通过bbed观察,freespace=1579的情况.
总结：
1.这种情况在实际系统很少见，仅仅出现在行记录很短的情况。 2.一旦发生行迁移，在被行迁移的块中会增加至少1个ITL槽,当然我的测试非常极端，出现增加10-20个ITL槽应该是正常的。 3.这种情况另外一个副产品就是itl不足，当然也出现在块写很慢，行记录很短的情况较常见。 4.从另外一个侧面说明为什么8K数据块Hakan Factor=736 ，要保留行迁移的记录nrid 6个字节+前面3个 5.其实这些测试并不重要，主要在于更好的理解oracle的一些内部结构。--补充测试：
)> update t set pad=lpad('a',10,'a') where id=4;
1 row updated.SCOTT@book(112,565)> commit ;
Commit complete.SCOTT@book(112,565)> alter system checkpoint ;
System altered.BBED> set dba 4,747
DBA 0x010002eb (16777963 4,747)BBED> x /rnc *kdbr[2]
rowdata[9] @3136 ---------- flag@3136: 0x20 (KDRHFH) lock@3137: 0x00 cols@3138: 0 nrid@3139:0x010002ee.1BBED> x /rnc *kdbr[3]
rowdata[0] @3127 ---------- flag@3127: 0x20 (KDRHFH) lock@3128: 0x01 cols@3129: 0 nrid@3130:0x010002ee.2--//最少预留9个字节。
转载地址：http://jhdox.baihongyu.com/