[20140213]再论行迁移.txt
昨天看jonathanlewis的blog,链接如下:
https://jonathanlewis.wordpress.com/2014/02/10/row-migration/
Oracle behaves quite intelligently with migrated rows. First, the migrated row has a pointer back to the original
location and if the row has to migrate a second time the first place that Oracle checks for space is the original block,
so the row might "de-migrate" itself; however, even if it can't migrate back to the original block, it will still
revisit the original block to change the pointer in that block to refer to the block it has moved on to – so the row
is never more than one step away from its original location. As a quick demonstration, here's some code to generate and
manipulate some data:
--我按照自己方式演示作者的例子。中间穿插我的说明。
1.测试环境:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
--表空间使用assm管理。SEGMENT SPACE MANAGEMENT AUTO。
create table t1 (id number(6,0),v1 varchar2(1200)) pctfree 0 ;
prompt ==========================================
prompt The following code fits 74 rows to a block
prompt ==========================================
insert into t1 select rownum - 1, rpad('x',100) from all_objects where rownum <= 75;
commit;
-------------------------------------------------------------------
SCOTT@test> select rowid,t1.id from t1;
ROWID ID
------------------ ----------
AABFP/AAEAAAAIOAAA 0
AABFP/AAEAAAAIOAAB 1
...
AABFP/AAEAAAAIOABJ 73
AABFP/AAEAAAAIPAAA 74
75 rows selected.
SCOTT@test> @lookup_rowid AABFP+AAEAAAAIOAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
283647 4 526 0 4,526 alter system dump datafile 4 block 526 ;
SCOTT@test> @lookup_rowid AABFP/AAEAAAAIPAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
283647 4 527 0 4,527 alter system dump datafile 4 block 527 ;
-- ID= 74 在另外1块,意味着dba=4,526的块是已经写满。在使用bbed观察。
BBED> set dba 4,526
DBA 0x0100020e (16777742 4,526)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @271 0x2c
BBED> x /rxncc
rowdata[0] @271
----------
flag@271: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@272: 0x01
cols@273: 2
col 0[1] @274: 0x80
col 1[100] @276: 0x78 0x20 0x20 0x20 0x20 .. --太长截断
--可以发现id=0的记录没有发生行迁移。*kdbr[0]=271.
------------------------------------------------------------------------
prompt ======================================
prompt Make the first row migrate and dump it
prompt ======================================
update t1 set v1 = rpad('x',400) where id = 0;
commit;
alter system flush buffer_cache;
-- execute dump_seg('t1',2)
-----------------------------------------------------------------------
--修改 id=0,增加长度400,这样该块放不下,会出现行迁移情况。我不使用dump,
--代替使用bbed观察.
BBED> x /rxncc
rowdata[0] @271
----------
flag@271: 0x20 (KDRHFH)
lock@272: 0x02
cols@273: 0
nrid@274:0x0100020f.1
--可以发现发生行迁移,cols=0,意味2个字段都移动到dba=0x0100020f.1
SCOTT@test> @dfb 0100020f
RFILE# BLOCK#
---------- ----------
4 527
TEXT
----------------------------------------
alter system dump datafile 4 block 527 ;
--也就是移动到了file#=5,block#=527的行号=1.
BBED> set dba 4,527
DBA 0x0100020f (16777743 4,527)
BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0] @7667 0x0c
BBED> x /rxncc
rowdata[0] @7667
----------
flag@7667: 0x0c (KDRHFL, KDRHFF)
lock@7668: 0x02
cols@7669: 2
hrid@7670:0x0100020e.0
col 0[1] @7676: 0x80
col 1[400] @7678: 0x78 0x20 0x20 0x20 0x20 --太长截断
-- 可以发现hrid = 0x0100020e.0,就是指向原来的位置。
SCOTT@test> @dfb 0100020e
RFILE# BLOCK#
---------- ----------
4 526
TEXT
----------------------------------------
alter system dump datafile 4 block 526 ;
-----------------------------------------------------------------------
prompt ===========================================================
prompt Fill the block the long row is now in, force it to migrate,
prompt then dump it again.
prompt ===========================================================
insert into t1 select rownum + 75, rpad('x',100) from all_objects where rownum <= 75;
commit;
update t1 set v1 = rpad('x',800) where id = 0;
commit;
alter system flush buffer_cache;
--execute dump_seg('t1',3)
-----------------------------------------------------------------------------
--在插入75条记录,是file#=4,block#=527填满。在修改id=0的记录长度增加到800,
--这样file#=4,block#=527已经无法放下这个长的记录,再次出现行迁移的情况。
BBED> set dba 4,526
DBA 0x0100020e (16777742 4,526)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @271 0x20
BBED> x /rxncc
rowdata[0] @271
----------
flag@271: 0x20 (KDRHFH)
lock@272: 0x01
cols@273: 0
nrid@274:0x0100020b.7
--这次nrid@274:0x0100020b.7,行迁移到别的地方。0x0100020b.7
SCOTT@test> @dfb 0100020b
RFILE# BLOCK#
---------- ----------
4 523
TEXT
----------------------------------------
alter system dump datafile 4 block 523 ;
--迁移到file#=4,block#=523的第7行。
BBED> set dba 4,523
DBA 0x0100020b (16777739 4,523)
BBED> p *kdbr[7]
rowdata[0]
----------
ub1 rowdata[0] @6618 0x0c
BBED> x /rxncc
rowdata[0] @6618
----------
flag@6618: 0x0c (KDRHFL, KDRHFF)
lock@6619: 0x02
cols@6620: 2
hrid@6621:0x0100020e.0
col 0[1] @6627: 0x80
col 1[800] @6629: 0x78 0x20 0x20 0x20 0x20 --太长截断
-- 可以发现hrid = 0x0100020e.0,就是指向原来的位置。而原来的行迁移,变成如下:
BBED> set dba 4,527
DBA 0x0100020f (16777743 4,527)
BBED> p *kdbr[1]
rowdata[7319]
-------------
ub1 rowdata[7319] @7667 0x1c
BBED> x /rxncc
rowdata[7319] @7667
-------------
flag@7667: 0x1c (KDRHFL, KDRHFF, KDRHFD)
lock@7668: 0x01
cols@7669: 0
-----------------------------------------------------------------------------
prompt ========================================================
prompt Fill the block the long row is now in and shrink the row
prompt to see if it returns to its original block. (It won't.)
prompt ========================================================
insert into t1 select rownum + 150, rpad('x',100) from all_objects where rownum <= 75;
commit;
update t1 set v1 = rpad('x',50) where id = 0;
commit;
alter system flush buffer_cache;
-- execute dump_seg('t1',3)
------------------------------------------------------------------------------
--再次插入75条记录填满file#=4,block#=523块。修改id=0,这回是长度变小为50.
BBED> set dba 4,526
DBA 0x0100020e (16777742 4,526)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @271 0x20
BBED> x /rxncc
rowdata[0] @271
----------
flag@271: 0x20 (KDRHFH)
lock@272: 0x02
cols@273: 0
nrid@274:0x0100020b.7
--虽然记录长度缩小,实际上nrid@274:0x0100020b.7,依旧没有变化。主要file#=4,block#=526无法容下变小的记录。
------------------------------------------------------------------------------
prompt ========================================================
prompt Make a lot of space in the first block and force the row
prompt to migrate again to see if it migrates back. (It does.)
prompt ========================================================
delete from t1 where id between 1 and 20;
commit;
update t1 set v1 = rpad('x',1200) where id = 0;
commit;
alter system flush buffer_cache;
-- execute dump_seg('t1',3)
------------------------------------------------------------------------
--删除id=1 到 20的记录,这样空出file#=4,block#=526的空间,这样再修改id=0的记录,
--即使长度增加到1200,这样可以容下,看看行迁移的情况。
BBED> set dba 4,526
DBA 0x0100020e (16777742 4,526)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @1309 0x2c
BBED> x /rxncc
rowdata[0] @1309
----------
flag@1309: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1310: 0x02
cols@1311: 2
col 0[1] @1312: 0x80
col 1[1200] @1314: 0x78 0x20 0x20 --太长截断。
--可以发现数据迁移回来了,这个纠正我原来的错误观点,发生了行迁移以后是回不来的。实际上是可以回来了。
--如果仔细看*kdbr[0]已经指向了@1309,而不是前面的@271.我们看看@271的情况
BBED> set offset 271
OFFSET 271
BBED> x /rxncc
freespace[5] @271
------------
flag@271: 0x20 (KDRHFH)
lock@272: 0x02
cols@273: 0
nrid@274:0x0100020b.7
BBED> set dba 4,523
DBA 0x0100020b (16777739 4,523)
BBED> p *kdbr[7]
rowdata[0]
----------
ub1 rowdata[0] @293 0x1c
BBED> x /rxncc
rowdata[0] @293
----------
flag@293: 0x1c (KDRHFL, KDRHFF, KDRHFD)
lock@294: 0x02
cols@295: 0
BBED> set offset 6618
OFFSET 6618
BBED> x /rxncc
rowdata[6325] @6618
-------------
flag@6618: 0x0c (KDRHFL, KDRHFF)
lock@6619: 0x00
cols@6620: 2
hrid@6621:0x0100020e.0
col 0[1] @6627: 0x80
col 1[800] @6629: 0x78 0x20 0x20 --截断。
------------------------------------------------------------------------
总结:
发生行迁移后,如果条件可以,行迁移是可以回来的。
使用的脚本:
--dfb.sql
select
dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx')) rfile#,
dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) block#
from dual;
select 'alter system dump datafile '||dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx'))||' block '||
dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) ||' ;' text
from dual;
--lookup_rowid.sql
set verify off
column dba format a20
column text format a40
SELECT DBMS_ROWID.ROWID_OBJECT ('&1') "OBJECT",
DBMS_ROWID.ROWID_RELATIVE_FNO ('&1') "FILE",
DBMS_ROWID.ROWID_BLOCK_NUMBER ('&1') "BLOCK",
DBMS_ROWID.ROWID_ROW_NUMBER ('&1') "ROW",
DBMS_ROWID.ROWID_RELATIVE_FNO ('&1')
|| ','
|| DBMS_ROWID.ROWID_BLOCK_NUMBER ('&1')
"DBA",
'alter system dump datafile '
|| DBMS_ROWID.ROWID_RELATIVE_FNO ('&1')
|| ' block '
|| DBMS_ROWID.ROWID_BLOCK_NUMBER ('&1')
|| ' ;'
text
FROM DUAL;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1080946/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-1080946/