溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

什么是SQL行遷移和行鏈接

發(fā)布時(shí)間：2021-11-05 10:51:13 來(lái)源：億速云 閱讀：130 作者：iii 欄目：關(guān)系型數(shù)據(jù)庫(kù)

本篇內(nèi)容介紹了“什么是SQL行遷移和行鏈接”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

行遷移和行鏈接（行的跨塊存儲(chǔ)）

行鏈接：

成因：指一行存儲(chǔ)在多個(gè)塊中的情況，即行鏈接是跨越多塊的行。

第一次插入時(shí)，一個(gè)塊存不下，insert 到多個(gè)塊中。

當(dāng)一行數(shù)據(jù)大于一個(gè)數(shù)據(jù)塊，ORACLE會(huì)同時(shí)分配兩個(gè)數(shù)據(jù)塊，并在第一個(gè)塊上登記第二個(gè)塊的地址，從而形成行鏈接。

后果：導(dǎo)致應(yīng)用需要訪問(wèn)更多的數(shù)據(jù)塊，性能下降。

預(yù)防：針對(duì)表空間擴(kuò)大數(shù)據(jù)塊大小。

檢查：analyze table 表名 validate structure cascade into chained_rows;

           需要執(zhí)行腳本$ORACLE_HOME/rdbms/admin/utlchain.sql創(chuàng)建chained_rows表。

行遷移：

成因：當(dāng)行被Update時(shí)，如果Update更新的行大于數(shù)據(jù)塊得PCTFREE值，就需要申請(qǐng)第2個(gè)塊，從而形成行遷移。

當(dāng)一個(gè)數(shù)據(jù)行由于 update 語(yǔ)句導(dǎo)致當(dāng)前塊被重新定位到另一個(gè)塊（那里有充足的空間）中，但在原始?jí)K中會(huì)保留一個(gè)指針。

原始?jí)K中的指針是必需的，因?yàn)樗饕?ROWID 項(xiàng)仍然指向原始位置。

行遷移是 update 語(yǔ)句當(dāng) pctfree 空間不足時(shí)引起的，它與 insert 和 delete 語(yǔ)句無(wú)關(guān)。

后果：導(dǎo)致應(yīng)用需要訪問(wèn)更多的數(shù)據(jù)塊，性能下降。

預(yù)防：1. 將數(shù)據(jù)塊的PCTFREE調(diào)大；

          2. 針對(duì)表空間擴(kuò)大數(shù)據(jù)塊大小

檢查：analyze table 表名 validate structure cascade into chained_rows;

如何知道發(fā)生了行鏈接或行遷移？

對(duì)表進(jìn)行analyze，然后查看 dba_tables 的 AVG_ROW_LEN 列和 CHAIN_CNT 列，若不為0，則說(shuō)明發(fā)生了行遷移或者行鏈接。

如何確定發(fā)生了行遷移還是行鏈接？

這里采用move的辦法：

若對(duì)表進(jìn)行move，然后CHAIN_CNT的列變?yōu)?，并且blocks也會(huì)減少，則說(shuō)明只發(fā)生了行遷移。

若對(duì)表進(jìn)行move，然后CHAIN_CNT的列不變，并且blocks也不變，則說(shuō)明只發(fā)生了行鏈接。

還有一種特殊情況，同時(shí)發(fā)生行遷移和行鏈接（比較極端）：

在對(duì)表進(jìn)行update時(shí)，更新的行過(guò)大，導(dǎo)致一個(gè)塊存不下，那么會(huì)變成行遷移，但是由于過(guò)大，因此還需要更多的塊來(lái)存儲(chǔ)，發(fā)生的是行鏈接

若對(duì)表進(jìn)行move，然后CHAIN_CNT的列不變或者減少，并且blocks減少，則說(shuō)明同時(shí)發(fā)生了行鏈接和行鏈接。

優(yōu)化：

行遷移：1、若表在進(jìn)行update時(shí)發(fā)生了行遷移，那么需要對(duì)表進(jìn)行move操作(若有索引，需要重建)

2、exp/imp方式（針對(duì)發(fā)生行遷移的表）

3、對(duì)發(fā)生行遷移的表的數(shù)據(jù)進(jìn)行新建臨時(shí)表，然后在把記錄插回到原表

行鏈接：只有通過(guò)加大BLOCK塊的方式才可以避免

行遷移測(cè)試：

創(chuàng)建表
SQL> drop table t1;
Table dropped.
SQL> create table t1 (c1 varchar2(20));
Table created.
插入數(shù)據(jù)
SQL> insert into t1 select '' from dual connect by level < 1000;
999 rows created.
SQL> commit;
Commit complete.
先分析一下 t1 表，確定無(wú)行遷移
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個(gè)塊，并且chain_cnt為0，并沒(méi)有發(fā)生行遷移。
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
說(shuō)明 999 行中有 767 行發(fā)生了行遷移，使用的塊也增加了。

消除行遷移：

這里使用 move 解決，若表上有索引，需要重建索引，move會(huì)使表上的索引失效：

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個(gè)塊，從13塊變成6，block減少，并且CHAIN_CNT由767變成0，此時(shí)已經(jīng)消除了行遷移。

總結(jié)：若表在進(jìn)行update時(shí)發(fā)生了行遷移，那么需要對(duì)表進(jìn)行行遷移的消除，可以采用move操作來(lái)消除行遷移(若有索引，需要重建)。

行鏈接測(cè)試：

創(chuàng)建表 
SQL> create table t2 (c1 varchar2(4000),c2 varchar2(4000),c3 varchar2(4000));
Table created.
插入數(shù)據(jù)
SQL> insert into t2 select 'a','b','c' from dual connect by level <= 100;
100 rows created.
SQL> commit;
Commit complete.
先分析一下 t1 表，確定無(wú)行鏈接
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個(gè)塊，并且chain_cnt為0，并沒(méi)有發(fā)生行鏈接。
插入大于 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

說(shuō)明 新插入的 1 行數(shù)據(jù)發(fā)生了行遷移，使用的塊不變。

消除行鏈接：

創(chuàng)建非標(biāo)準(zhǔn)塊大小表空間 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
設(shè)置db_16k_cache_size為16m
SQL> alter system set db_16k_cache_size=16m;
System altered.
創(chuàng)建表空間
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表移動(dòng)到tabspace_16k表空間上
SQL> alter table t2 move tablespace tabspace_16k;
Table altered.
再次進(jìn)行分析查看
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個(gè)塊，并且chain_cnt為0，說(shuō)明行鏈接已經(jīng)消除了。

同時(shí)發(fā)生行遷移和行鏈接（比較極端）：

創(chuàng)建表
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.
插入數(shù)據(jù)
SQL> insert into t3 select level,'','','','' from dual connect by level <= 100;
100 rows created.
SQL> commit;
Commit complete.
先分析一下 t3 表，確定無(wú)行遷移和行鏈接
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個(gè)塊，并且chain_cnt為0，并沒(méi)有發(fā)生行遷移或者行鏈接
對(duì)列進(jìn)行更新，再分析 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

說(shuō)明 100 行中有 100 行發(fā)生了行遷移或者行鏈接，使用的塊也增加了。

如何確定是發(fā)生了行遷移還是行鏈接？

這里采用move的辦法：

若對(duì)表進(jìn)行move，然后CHAIN_CNT的列變?yōu)?，并且blocks也會(huì)減少，則說(shuō)明只發(fā)生了行遷移。

若對(duì)表進(jìn)行move，然后CHAIN_CNT的列不變，并且blocks也不變，則說(shuō)明只發(fā)生了行鏈接。

還有一種特殊情況，同時(shí)發(fā)生行遷移和行鏈接（比較極端）：

在對(duì)表進(jìn)行update時(shí)，更新的行過(guò)大，導(dǎo)致一個(gè)塊存不下，那么會(huì)變成行遷移，但是由于過(guò)大，因此還需要更多的塊來(lái)存儲(chǔ)，發(fā)生的是行鏈接

若對(duì)表進(jìn)行move，然后CHAIN_CNT的列不變或者減少，并且blocks減少，則說(shuō)明同時(shí)發(fā)生了行鏈接和行鏈接。

消除行遷移：

這里使用 move 解決，若表上有索引，需要重建索引，move會(huì)使表上的索引失效：
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個(gè)塊，從496塊變成234，block減少，并且CHAIN_CNT沒(méi)變，說(shuō)明已經(jīng)消除了行遷移，但是還有行鏈接為消除。

因此，同時(shí)發(fā)生行遷移和行鏈接。

下一步需要消除行鏈接：

和正常消除行鏈接的方法一致：

創(chuàng)建非標(biāo)準(zhǔn)塊大小表空間 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
設(shè)置db_16k_cache_size為16m
SQL> alter system set db_16k_cache_size=16m;
System altered.
創(chuàng)建表空間
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表移動(dòng)到tabspace_16k表空間上
SQL> alter table t3 move tablespace tabspace_16k;
Table altered.
再次進(jìn)行分析查看
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個(gè)塊，并且chain_cnt為0，說(shuō)明行鏈接已經(jīng)消除了。

至此消除了行遷移和行鏈接。

“什么是SQL行遷移和行鏈接”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

sql
AI
助
手