[20140213]再論行遷移.txt
[20140213]再論行遷移.txt
昨天看jonathanlewis的blog，連結如下：
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180327]行遷移與ITL浪費.txt
- [20190120]行連結遷移與dml.txt
- [20230425]CBO cost與行遷移關係.txt
- [20180402]行連結行遷移與ITL槽6.txt
- [論文閱讀] 顏色遷移-N維pdf遷移
- [20180727]再論count(*)和count(1).txt
- [20210418]CBC latch再討論3.txt
- [20210419]CBC latch再討論4.txt
- [20210413]CBC latch再討論2.txt
- 再談遷移學習：微調網路遷移學習
- 遷移執行緒migration執行緒
- [20181105]再論12c set feedback only.txt
- [20220105]再論ORA-29275與toad 12.txt
- [20211229]再論19c latch free等待事件分析.txt事件
- [論文閱讀] 顏色遷移-Correlated Color Space
- 模擬Oracle行遷移和行連結Oracle
- Perceptual Losses 風格遷移論文復現小記
- 再無需從頭訓練遷移學習模型！亞馬遜開源遷移學習資料庫 Xfer遷移學習模型亞馬遜資料庫
- Oracle 行遷移 & 行連結的檢測與消除Oracle
- [論文閱讀] 顏色遷移-Linear Monge-Kantorovitch(MKL)
- 棧遷移
- 遷移公告
- EF Core 小技巧：遷移已經應用到資料庫，如何進行遷移回退操作？資料庫
- 使用Mobilenet和Keras進行遷移學習！Keras遷移學習
- 杉巖資料銀行Documentum遷移方案
- 使用Conda Pack進行環境打包遷移
- 【遷移】SqlServer 遷移到 MySQL 方法ServerMySql
- “遷移策略+新容器執行時”應對有狀態應用的冷熱遷移挑戰
- 1.5 使用nvicat和kettle進行全量遷移
- docker映象遷移Docker
- 賬號遷移
- Jenkins Job遷移Jenkins
- Oracle資料庫中資料行遷移與行連結Oracle資料庫
- 從 MySQL 到 WuTongDB 的遷移指導：理論與實踐框架MySql框架
- 基於GAN的字型風格遷移 | CVPR 2018論文解讀
- 從 Oracle 到 TiDB，全鏈路資料遷移平臺核心能力和杭州銀行遷移實踐OracleTiDB
- Kubernetes怎麼進行NFS動態儲存遷移NFS
- Terraform初探：遷移本地專案到Terraform Cloud執行ORMCloud
- 使用dbeaver 用csv 檔案進行資料遷移