溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“什么是SQL行遷移和行鏈接”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
行鏈接：
成因：指一行存儲在多個塊中的情況，即行鏈接是跨越多塊的行。
第一次插入時，一個塊存不下，insert 到多個塊中。
當一行數據大于一個數據塊，ORACLE會同時分配兩個數據塊，并在第一個塊上登記第二個塊的地址，從而形成行鏈接。
后果：導致應用需要訪問更多的數據塊，性能下降。
預防：針對表空間擴大數據塊大小。
檢查：analyze table 表名 validate structure cascade into chained_rows;
需要執行腳本$ORACLE_HOME/rdbms/admin/utlchain.sql創建chained_rows表。
行遷移：
成因：當行被Update時，如果Update更新的行大于數據塊得PCTFREE值，就需要申請第2個塊，從而形成行遷移。
當一個數據行由于 update 語句導致當前塊被重新定位到另一個塊（那里有充足的空間）中，但在原始塊中會保留一個指針。
原始塊中的指針是必需的，因為索引的 ROWID 項仍然指向原始位置。
行遷移是 update 語句當 pctfree 空間不足時引起的，它與 insert 和 delete 語句無關。
后果：導致應用需要訪問更多的數據塊，性能下降。
預防：1. 將數據塊的PCTFREE調大；
2. 針對表空間擴大數據塊大小
檢查：analyze table 表名 validate structure cascade into chained_rows;
如何知道發生了行鏈接或行遷移？
對表進行analyze，然后查看 dba_tables 的 AVG_ROW_LEN 列和 CHAIN_CNT 列，若不為0，則說明發生了行遷移或者行鏈接。
如何確定發生了行遷移還是行鏈接？
這里采用move的辦法：
若對表進行move，然后CHAIN_CNT的列變為0，并且blocks也會減少，則說明只發生了行遷移。
若對表進行move，然后CHAIN_CNT的列不變，并且blocks也不變，則說明只發生了行鏈接。
還有一種特殊情況，同時發生行遷移和行鏈接（比較極端）：
在對表進行update時，更新的行過大，導致一個塊存不下，那么會變成行遷移，但是由于過大，因此還需要更多的塊來存儲，發生的是行鏈接
若對表進行move，然后CHAIN_CNT的列不變或者減少，并且blocks減少，則說明同時發生了行鏈接和行鏈接。
優化：
行遷移：1、若表在進行update時發生了行遷移，那么需要對表進行move操作(若有索引，需要重建)
2、exp/imp方式（針對發生行遷移的表）
3、對發生行遷移的表的數據進行新建臨時表，然后在把記錄插回到原表
行鏈接：只有通過加大BLOCK塊的方式才可以避免
創建表 SQL> drop table t1; Table dropped. SQL> create table t1 (c1 varchar2(20)); Table created. 插入數據 SQL> insert into t1 select '' from dual connect by level < 1000; 999 rows created. SQL> commit; Commit complete. 先分析一下 t1 表，確定無行遷移 SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 SQL> analyze table t1 compute statistics; Table analyzed. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 3 0 5 使用了5個塊，并且chain_cnt為0，并沒有發生行遷移。 update空列，再分析 t1,有了行遷移 SQL> update t1 set c1='oracle mysql'; 999 rows updated. SQL> commit; Commit complete. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 3 0 5 SQL> analyze table t1 compute statistics; Table analyzed. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 21 767 13 說明 999 行中有 767 行發生了行遷移，使用的塊也增加了。
這里使用 move 解決，若表上有索引，需要重建索引，move會使表上的索引失效：
SQL> alter table t1 move; Table altered. move 表后，再分析 t1,行遷移消失。 SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 21 767 13 SQL> analyze table t1 compute statistics; Table analyzed. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 16 0 6
使用了6個塊，從13塊變成6，block減少，并且CHAIN_CNT由767變成0，此時已經消除了行遷移。
總結：若表在進行update時發生了行遷移，那么需要對表進行行遷移的消除，可以采用move操作來消除行遷移(若有索引，需要重建)。
創建表
SQL> create table t2 (c1 varchar2(4000),c2 varchar2(4000),c3 varchar2(4000));
Table created.
插入數據
SQL> insert into t2 select 'a','b','c' from dual connect by level <= 100;
100 rows created.
SQL> commit;
Commit complete.
先分析一下 t1 表，確定無行鏈接
SQL> select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2';
PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS
---------- ----------- ---------- ----------
10
SQL> analyze table t2 compute statistics;
Table analyzed.
SQL> select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2';
PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS
---------- ----------- ---------- ----------
10 9 0 5
使用了5個塊，并且chain_cnt為0，并沒有發生行鏈接。
插入大于 8K 行，再分析 t2,有了行鏈接
SQL> insert into t2 values (lpad('a',4000,'a'),lpad('a',4000,'a'),lpad('a',4000,'a'));
1 row created.
SQL> commit;
Commit complete.
SQL> select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2';
PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS
---------- ----------- ---------- ----------
10 9 0 5
SQL> analyze table t2 compute statistics;
Table analyzed.
SQL> select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2';
PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS
---------- ----------- ---------- ----------
10 128 1 5說明 新插入的 1 行數據發生了行遷移，使用的塊不變。
創建非標準塊大小表空間 16K SQL> show parameter cache NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ client_result_cache_lag big integer 3000 client_result_cache_size big integer 0 db_16k_cache_size big integer 0 db_2k_cache_size big integer 0 db_32k_cache_size big integer 1G db_4k_cache_size big integer 0 db_8k_cache_size big integer 0 db_cache_advice string ON db_cache_size big integer 0 db_flash_cache_file string db_flash_cache_size big integer 0 db_keep_cache_size big integer 0 db_recycle_cache_size big integer 0 object_cache_max_size_percent integer 10 object_cache_optimal_size integer 102400 result_cache_max_result integer 5 result_cache_max_size big integer 12064K result_cache_mode string MANUAL result_cache_remote_expiration integer 0 session_cached_cursors integer 50 設置db_16k_cache_size為16m SQL> alter system set db_16k_cache_size=16m; System altered. 創建表空間 create tablespace tabspace_16k blocksize 16K datafile '/oracle/app/oracle/oradata/test/tabspace_16k.dbf' size 20M autoextend on extent management local segment space management auto; 把t2表移動到tabspace_16k表空間上 SQL> alter table t2 move tablespace tabspace_16k; Table altered. 再次進行分析查看 SQL> select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2'; PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ----------- ---------- ---------- 10 128 1 5 SQL> analyze table t2 compute statistics; Table analyzed. SQL> select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2'; PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ----------- ---------- ---------- 10 128 0 4
目前使用了4個塊，并且chain_cnt為0，說明行鏈接已經消除了。
創建表
SQL> drop table t3;
Table dropped.
SQL> create table t3 (c1 int,c2 varchar2(4000),c3 varchar2(4000),c4 varchar2(4000),c5 varchar2(4000));
Table created.
插入數據
SQL> insert into t3 select level,'','','','' from dual connect by level <= 100;
100 rows created.
SQL> commit;
Commit complete.
先分析一下 t3 表，確定無行遷移和行鏈接
SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3';
PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS
---------- ---------- ----------- ---------- ----------
10
SQL> analyze table t3 compute statistics;
Table analyzed.
SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3';
PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS
---------- ---------- ----------- ---------- ----------
10 6 0 5
使用了5個塊，并且chain_cnt為0，并沒有發生行遷移或者行鏈接
對列進行更新，再分析 t1,有了行遷移或者行鏈接
SQL> update t3 set c2 = LPAD('1', 4000, '*'),c3 = LPAD('1', 4000, '*'),c4 = LPAD('1', 4000, '*'),c5 = LPAD('1', 4000, '*');
100 rows updated.
SQL> commit;
Commit complete.
SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3';
PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS
---------- ---------- ----------- ---------- ----------
10 6 0 5
SQL> analyze table t3 compute statistics;
Table analyzed.
SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3';
PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS
---------- ---------- ----------- ---------- ----------
10 16030 100 496說明 100 行中有 100 行發生了行遷移或者行鏈接，使用的塊也增加了。
這里采用move的辦法：
若對表進行move，然后CHAIN_CNT的列變為0，并且blocks也會減少，則說明只發生了行遷移。
若對表進行move，然后CHAIN_CNT的列不變，并且blocks也不變，則說明只發生了行鏈接。
還有一種特殊情況，同時發生行遷移和行鏈接（比較極端）：
在對表進行update時，更新的行過大，導致一個塊存不下，那么會變成行遷移，但是由于過大，因此還需要更多的塊來存儲，發生的是行鏈接
若對表進行move，然后CHAIN_CNT的列不變或者減少，并且blocks減少，則說明同時發生了行鏈接和行鏈接。
這里使用 move 解決，若表上有索引，需要重建索引，move會使表上的索引失效： SQL> alter table t3 move; Table altered. move 表后，再分析 t3 SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 16030 100 496 SQL> analyze table t3 compute statistics; Table analyzed. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 16044 100 234
使用了234個塊，從496塊變成234，block減少，并且CHAIN_CNT沒變，說明已經消除了行遷移，但是還有行鏈接為消除。
因此，同時發生行遷移和行鏈接。
和正常消除行鏈接的方法一致：
創建非標準塊大小表空間 16K SQL> show parameter cache NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ client_result_cache_lag big integer 3000 client_result_cache_size big integer 0 db_16k_cache_size big integer 0 db_2k_cache_size big integer 0 db_32k_cache_size big integer 1G db_4k_cache_size big integer 0 db_8k_cache_size big integer 0 db_cache_advice string ON db_cache_size big integer 0 db_flash_cache_file string db_flash_cache_size big integer 0 db_keep_cache_size big integer 0 db_recycle_cache_size big integer 0 object_cache_max_size_percent integer 10 object_cache_optimal_size integer 102400 result_cache_max_result integer 5 result_cache_max_size big integer 12064K result_cache_mode string MANUAL result_cache_remote_expiration integer 0 session_cached_cursors integer 50 設置db_16k_cache_size為16m SQL> alter system set db_16k_cache_size=16m; System altered. 創建表空間 create tablespace tabspace_16k blocksize 16K datafile '/oracle/app/oracle/oradata/test/tabspace_16k.dbf' size 20M autoextend on extent management local segment space management auto; 把t2表移動到tabspace_16k表空間上 SQL> alter table t3 move tablespace tabspace_16k; Table altered. 再次進行分析查看 SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 16044 100 234 SQL> analyze table t3 compute statistics; Table analyzed. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 16018 0 107
目前使用了107個塊，并且chain_cnt為0，說明行鏈接已經消除了。
至此消除了行遷移和行鏈接。
“什么是SQL行遷移和行鏈接”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
