行遷移檢測及解決一例

行遷移(row migration)：由於在table 的pctfree 設定過小，在update 這個table並使其行長增大的時候，就有可能因為塊的剩餘空間不夠儲存該行，oracle會把該行資料遷移到另外一個有足夠空閒空間的block中，此即發生了行遷移；發生行遷移時，行rowid並不變，原先儲存該行的地方增加了一個新的指標，該指標指向遷移後的block id，所以我們在訪問發生了行遷移的行時，會要讀取遷移前和遷移後的2個block，一個表如果有大量的行發生了row migration，那麼就有必要檢視該table的storage 設定了，看看pctpree是否可以調整的更大一點。

連結(row chained)：和行遷移相對應的是行連結 row chained ，行連線常發生在行很大的情況下，如有long，raw，lob列，當insert 一條新的記錄時，一個block空間不夠容納一條記錄，oracle會連結若干個block用來儲存此記錄，此即稱為行連結；行連結是應為行的長度太長，要想避免，只能增加愛block size，在oracle9i 之前，建立好資料庫後您無法修改block size，要求你在建庫的時候就要規劃好，在oracle9i，提供了多塊大小的技術，您可以設定2k,4k,8k,16k,32k大小的block，若想使用，您還必須設定相應的db_nk_cache_size 緩衝池的大小。

下面是應用中客戶維錶行遷移的檢測和消除步驟

檢查user_tables 資訊

Select table_name,pct_free,num_rows,blocks,chain_cnt From user_tables Where table_name='CUST_DIMT0';

TABLE_NAME PCT_FREE NUM_ROWS BLOCKS CHAIN_CNT

------------------------------ ---------- ---------- ---------- ----------

CUST_DIMT0 20 363338 8846 7449

利用oracle自帶的指令碼utlchain.sq，新增表chained_rows，用於儲存發生行遷移記錄資訊

SQL> @ $ORACLE_HOME/rdbms/admin/utlchain.sql

Table created.

SQL> grant all on chained_rows to dwh;

Grant succeeded.

用analyze 命令分析該表

SQL> analyze table cust_dimt0 list chained rows into sys.chained_rows;

Table analyzed

Chained_rows 用於存放產生行遷移的記錄資訊，其中head_rowid表示產生行遷移記錄的rowid；

從該表中任意找一個head_rowed,看看行遷移消除前的執行計劃

SQL> set autotrace traceonly;

SQL> select * from cust_dimt0 where rowid='AAAF9UAARAAAAWGAA3';

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=172)

1 0 TABLE ACCESS (BY USER ROWID) OF 'CUST_DIMT0' (Cost=1 Card=1 Bytes=172)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

1467 bytes sent via SQL*Net to client

275 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

可以看到，取一條記錄需要2個consistent read ;

接下來，來消除該表上的行遷移

修改表的pct_free，增大為25，日後需再觀察，是否有大量的行遷移情況，如有，則說明還需要調整該引數。

SQL> alter table cust_dimt0 pctfree 25;

建立臨時表，存放發生行遷移的記錄

CREATE TABLE CUST_DIMT0_080331 AS SELECT * FROM CUST_DIMT0 WHERE ROWID IN (SELECT HEAD_ROWID FROM SYS.CHAINED_ROWS WHERE TABLE_NAME='CUST_DIMT0');

檢視是否有外來鍵約束應用於此表上，如果有。則先禁用此約束，操作結束後再啟用

SQL> select constraint_name,constraint_type,r_owner,r_constraint_name from dba_constraints where table_name='CUST_DIMT0' and wner='DWH';

CONSTRAINT_NAME CONSTRAINT_TYPE R_OWNER R_CONSTRAINT_NAME

------------------------------ --------------- ------------------------------ ------------------------------

CUST_DIMT0_PK P

SYS_C006476 C

SYS_C006477 C

SYS_C006478 C

SYS_C006479 C

SQL> SELECT * FROM DBA_CONSTRAINTS WHERE R_CONSTRAINT_NAME='CUST_DIMT0_PK';

未選定行

如有相關表建有外來鍵指向該表，需要先禁用約束

alter table xxx disable constraint yyyy

刪除發生行遷移的記錄

SQL> DELETE CUST_DIMT0 WHERE ROWID IN (SELECT HEAD_ROWID FROM SYS.CHAINED_ROWS WHERE TABLE_NAME='CUST_DIMT0');

從臨時表中重新插入記錄

SQL> INSERT INTO CUST_DIMT0 SELECT * FROM CUST_DIMT0_080331;

看看執行行遷移消除後的執行計劃

SQL> select * from cust_dimt0 where rowid='AAAF9UAARAAAAWGAA3';

未選定行

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=172)

1 0 TABLE ACCESS (BY USER ROWID) OF 'CUST_DIMT0' (Cost=1 Card=1 Bytes=172)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

1 consistent gets

0 physical reads

0 redo size

1222 bytes sent via SQL*Net to client

234 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

可以發現，執行行遷移消除後，consistent gets 為1個block了。

最後，drop 臨時表

SQL> drop table CUST_DIMT0_080331;

Table dropped

注：行遷移主要是由於update 的時候，塊剩餘空間滿足不了而導致的，對於行遷移的消除，除了上面介紹的方法外，採用exp/imp ,先把表匯出，然後再匯入，或者採用move命令，同樣，可以達到行遷移消除的目的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10159839/viewspace-220330/，如需轉載，請註明出處，否則將追究法律責任。

行遷移檢測及解決一例

CUST_DIMT0_PK P

相關文章