Oracle行遷移和行連結

標籤：database   oracle   效能   資料庫   

行遷移和行連結都會導致Oracle效能下降，這篇文章將介紹什麼是行遷移和行連結，它們帶來的問題，如何來判斷它們，並提供瞭解決它們的辦法。

什麼是行遷移和行連結行遷移

Oracle的資料區塊會保留部分空間供以後更新使用，通常的資料區塊結構如下：

PCTFREE定義一個塊保留的空間百分比，預設是10，表示當資料區塊的可用空間低於10%後，就不可以被insert了，只能被update（具體看下面的PCTFREE介紹）。
當一條記錄被更新時，資料庫引擎首先會嘗試在它儲存的資料區塊中尋找足夠的空閑空間，如果沒有足夠的空閑空間可用，這條記錄將被拆分為兩個部分，第一個部分進包括指向第二個部分的rowid，該部分任然保留在原來的資料區塊中，第二個部分包含所有的具體資料，將儲存到另外一個新的資料區塊中，這個就成為行遷移。

為什麼不將整行都放到新的資料區塊中？
原因是這樣會導致該行資料rowid發生變化，而rowid被儲存在索引中，也有可能被用戶端臨時儲存在記憶體中，rowid的變化可能導致查詢錯誤。

PCTFREE介紹

PCTFREE可以在建表的時候指定：

create table test1 pctfree 20 as select rownum as id from all_objects where rownum <= 1000;

這裡指定表test1的PCTFREE為20，通過下面的方式可以查看該值：

select table_name, pct_free from user_tables where table_name = 'TEST1';TABLE_NAMEPCT_FREE---------------------------TEST120

在建立了表格後你可以通過alter table來調整PCTFREE值：

alter table test1 pctfree 15;

行連結

行連結和行遷移不同，行連結是當一條記錄太大，在一個資料區塊中無法存入，這時會被拆分為2個或以上的部分，儲存在多個塊中，這多個塊之間會構造一個鏈，如下：

行遷移是由於更新導致的，而行連結的原因則可能為：
 1）直接插入大的記錄；
 2）更新記錄導致記錄大於一個資料區塊，在這時，這樣記錄可能會同時變為行遷移和行連結。

行遷移和行連結帶來的問題

行遷移不會影響全掃描（全掃描更多介紹請看“Oracle效能分析4：資料存取方法之全掃描”），因為第一個部分不包含資料，會被直接跳過；但對於通過rowid進行訪問（索引掃描或者直接使用rowid查詢），則開銷會翻倍，主要由於一次讀取需要訪問兩個塊。
行連結則和資料訪問方式無關，每次訪問到第一個記錄片段之後，都需要通過rowid去訪問其他的記錄片段。
行遷移和行連結也會影響行級鎖，因為每個記錄片段都需要持有鎖，鎖的開銷和記錄片段的個數的增長成正比。

確定行遷移和行連結

確定行遷移和行連結可以使用下面幾種方法。

查看v$sysstat和v$sesstat視圖

該視圖中統計項table fetch continued row可以確認是否出現了行遷移和行連結。

select name,value from v$sysstat where name = 'table fetch continued row';NAMEVALUE-----------------------------------------------table fetch continued row27455

但這個值只能提示你資料庫的某個地方存在行遷移或者行連結，如果要評估導致的影響，你需要和table scan rows gotten和table fetch by rowid的統計資訊對比。

查看具體表的行遷移和行連結資訊

對指定表執行：

analyze table <table_name> list chained rows

如果發現了行連結或者行遷移的記錄，它們的rowid就會被記錄到CHAINED_ROWS這張表中，該表可以使用$ORACLE_HOME/rdbms/admin目錄下的UTLCHAIN.SQL或UTLCHN1.SQL指令碼建立，建表語句如下：

create table CHAINED_ROWS (  owner_name         varchar2(30),  table_name         varchar2(30),  cluster_name       varchar2(30),  partition_name     varchar2(30),  subpartition_name  varchar2(30),  head_rowid         urowid,  analyze_timestamp  date);

下面的預存程序將可以用來分析合格所有表：

begin  for obj in (select owner, object_name                from dba_objects               where object_type = 'TABLE'                 and <other conditions>) loop    dbms_output.put_line(obj.owner || '.' || obj.object_name);    execute immediate 'analyze table ' || obj.owner || '.' ||                      obj.object_name || ' list chained rows';  end loop;end;

然後通過查看CHAINED_ROWS表中的資料發現哪些表中存在行遷移和行連結：

select table_name,head_rowid from CHAINED_ROWSTABLE_NAMEHEAD_ROWID--------------------------------------IND$AAAAACAABAAAAAdAAAIND$AAAAACAABAAAAAdAAGIND$AAAAACAABAAAAAmAAIIND$AAAAACAABAAAGpRAAHIND$AAAAACAABAAAN0lAADIND$AAAAACAABAAAN0oAAE......

在這裡我們可以看到那些表的那些行存在行遷移和行連結，但並不知道具體是行遷移和行連結，我們可以通過計算記錄的長度，再將該長度和塊大小進行比較，從而識別出他們具體是行遷移還是行連結。
計算一行的長度使用下面的語句：

select vsize(<col1>) + vsize(<col2>) + ... + vsize(<col3>) from <table> where rowid = '<rowid>';

資料庫的塊大小在參數db_block_size中儲存，參看參數的方法詳見“Oracle參數查看方法小結”。

表統計資訊中查看行遷移和行連結

表DBA_tables的chain_cnt欄位表示行遷移和行連結的數量資訊，但dbms_stats包不會收集這個統計資訊，該值始終為0。

select table_name,chain_cnt from dba_tables where table_name = 'IND$';TABLE_NAMECHAIN_CNT---------------------------IND$0

通過下面的SQL語句可以收集該資訊：

analyze table ind$ compute statistics;

然後再次查看：

select table_name,chain_cnt from dba_tables where table_name = 'IND$';TABLE_NAMECHAIN_CNT---------------------------IND$13

但該方法會導致被分析表的所有對象的統計資訊都被覆蓋，因此，在實踐中不推薦使用。

解決辦法

行遷移和行連結的解決辦法不同，因此在處理前一定要區分清楚是行遷移還是行連結。

行遷移

首先我們應該避免行遷移，方法是在原塊中保留足夠的空閑空間，即調整PCTFREE參數值，值的大小需要評估記錄擴充的平均大小。
當出現了行遷移後，則只能通過移動資料來解決，具體的方式有：
 1）通過匯出、匯入或者ALTER TABLE MOVE對錶進行重整；
 2）將遷移的資料複製到暫存資料表中，在原表上刪除再重新插入這些資料。

行連結

處理行連結只能增加資料區塊的大小，但在一些情況下，可以通過將常用欄位放在表的前面，不常訪問的欄位放在表的末尾來提高某些查詢的效率（由於Oracle查詢時只會取查詢相關的欄位）。

Oracle行遷移和行連結