ITPub博客

首页 > 数据库 > Oracle > [20140213]再论行迁移.txt

[20140213]再论行迁移.txt

原创 Oracle 作者:lfree 时间:2014-02-13 15:58:35 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6290306