[20160803]另类行迁移.txt
--前几天做测试时,链接:
http://blog.itpub.net/267265/viewspace-2122712/=>[20160729]行链接行迁移与ITL槽4.txt
--发现一个块中的记录在字段长度变长后全部发生行迁移,感觉很奇怪,当时也没有仔细思考(开始以为至少有一些记录不会发生行迁移的
--情况),事后才想起来以前我做过类似的测试,参考链接:
http://blog.itpub.net/267265/viewspace-1742243/=> [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 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
------------------ ----------
AAAXWyAAEAAAALrAAA 1
AAAXWyAAEAAAALrALc 733
AAAXWyAAEAAAALsAAA 734
SCOTT@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: 1
BBED> 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 0x010002f9
167+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 @18
struct 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 @44
struct 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 @4120
struct kdbt[1], 4 bytes @4122
sb2 kdbtoffs @4122
sb2 kdbtnrow @4124
sb2 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 17
3.继续演示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 734
SCOTT@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的一些内部结构。
--补充测试:
SCOTT@book(112,565)> 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.1
BBED> x /rnc *kdbr[3]
rowdata[0] @3127
----------
flag@3127: 0x20 (KDRHFH)
lock@3128: 0x01
cols@3129: 0
nrid@3130:0x010002ee.2
--//最少预留9个字节。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2122887/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2122887/