行遷移_行連結的介紹
一、行遷移/行連結的介紹
在實際的工作中我們經常會碰到一些Oracle資料庫效能較低的問題，當然，
引起Oracle資料庫效能較低的原因是多方面的，Row Migration (行遷移) & Row Chaining (行連結)
就是其中我們可以儘量避免的引起Oracle資料庫效能低下的潛在問題。透過合理的診斷行遷移/行連結，
我們可以較大幅度上提高Oracle資料庫的效能。
那究竟什麼是行遷移/行連結呢，先讓我們從Oracle的block開始談起。
作業系統的最小讀寫操作單元是作業系統的block，所以當建立一個Oracle資料庫的時候我們應該
講資料庫的block size設定成為作業系統的block size的整數倍，Oracle block是Oracle資料庫中讀寫操作的最小單元，
Oracle9i之前的Oracle資料庫版本中Oracle block一旦在建立資料庫的時候被設定後就沒法再更改。
為了在建立資料庫之前確定一個合理的Oracle block的大小，我們需要考慮一些因素，例如資料庫本身
的大小以及併發事務的數量等。使用一個合適的Oracle block大小對於資料庫的調優是非常重要的。
一個Oracle block由三個部分組成，分別是資料塊頭、自由空間、實際資料三部份組成。
資料塊頭：主要包含有資料塊地址的一些基本資訊和段的型別，以及表和包含有資料的實際行的地址。
自由空間：是指可以為以後的更新和插入操作分配的空間，大小由PCTFREE和PCTUSED兩個引數影響。
實際資料：是指在行記憶體儲的實際資料。
當建立或者更改任何表和索引的時候，Oracle在空間控制方面使用兩個儲存引數：
PCTFREE:為將來更新已經存在的資料預留空間的百分比。
PCTUSED:用於為插入一新行資料的最小空間的百分比。這個值決定了塊的可用狀態。可用的塊時可以執行插入的塊，
不可用狀態的塊只能執行刪除和修改，可用狀態的塊被放在freelist中。
當表中一行的資料不能在一個資料block中放入的時候，這個時候就會發生兩種情況，一種是行連結，另外一種就是行遷移了。
行連結產生在第一次插入資料的時候如果一個block不能存放一行記錄的情況下。這種情況下，
Oracle將使用連結一個或者多個在這個段中保留的block儲存這一行記錄，行連結比較容易發生在比較大的行上，
例如行上有LONG、LONG RAW、LOB等資料型別的欄位，這種時候行連結是不可避免的會產生的。
當一行記錄初始插入的時候事可以儲存在一個block中的，由於更新操作導致行長增加了，
而block的自由空間已經完全滿了，這個時候就產生了行遷移。在這種情況下，Oracle將會遷移整行資料到一
個新的block中（假設一個block中可以儲存下整行資料），Oracle會保留被遷移行的原始指標指向新的存放行資料的block，
這就意味著被遷移行的ROW ID是不會改變的。
當發生了行遷移或者行連結，對這行資料操作的效能就會降低，因為Oracle必須要掃描更多的block來獲得這行的資訊。
下面舉例來具體說明行遷移/行連結的產生過程。
先建立一個pctfree為20和pctused為50的測試表：
create table test（
col1 char(20),
col2 number)
storage (
pctfree 20
pctused 50);
當插入一條記錄的時候，Oracle會在free list中先去尋找一個自由的塊，並且將資料插入到這個自由塊中。
而在free list中存在的自由的塊是由pctfree值決定的。初始的空塊都是在free list中的，直到塊中的自由空間
達到pctfree的值，此塊就會從free list中移走，而當此塊中的使用空間低於pctused的時候，此塊又被重新放到free list中。
Oracle使用free list機制可以大大的提高效能，對於每次的插入操作，Oracle只需要查詢free list就可以了，
而不是去查詢所有的block來尋找自由空間。
具體我們透過下面的一個試驗來檢視行連結和行遷移是如何產生並在資料檔案中體現出來的。先檢視ALLAN這個
表空間的資料檔案號，為了便於測試，我只建立了一個資料檔案。
SQL> select file_id from dba_data_files where tablespace_name='ALLAN';
FILE_ID
----------
23
建立一個測試表test：
SQL> create table test ( x int primary key,
a char(2000), b char(2000), c char(2000),
d char(2000), e char(2000) ) tablespace allan;
Table created.
因為我的資料庫的db_block_size是8K，所以我建立的表有五個欄位，每個佔2000個位元組，這樣一行記錄大約10K,
就能超過一個block的大小了。
然後插入一行記錄，只有一個欄位的：
SQL> insert into test(x) values (1);
1 row created.
SQL> commit;
Commit complete.
查詢這行記錄所在的block，並dump出來：
SQL> select dbms_rowid.rowid_block_number(rowid) from test;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
34
SQL> alter system dump datafile 23 block 34;
System altered.
在udump目錄下檢視trace檔案的內容如下：
Start dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
buffer tsn: 34 rdba: 0x05c00022 (23/34)
scn: 0x0000.013943f3 seq: 0x01 flg: 0x02 tail: 0x43f30601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x05c00022
Object id on Block? Y
seg/obj: 0x3ccd csc: 0x00.13943ef itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.02e.00000ad7 0x00800036.03de.18 --U- 1 fsc 0x0000.013943f3
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0xadb505c
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0adb505c
bdba: 0x05c00022
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f9a
avsp=0x1f83
tosp=0x1f83
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f9a
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 02
end_of_block_dump
End dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
對其中的一些資訊做一些解釋：
Fb:H是指行記錄的頭，L是指行記錄的最後一列，F是指行記錄的第一列。
Cc：列的數量。
Nrid：對於行連結或者行遷移來說的下一個row id的值。
由上面的dump資訊我們可以看出來當前表test是沒有行連結或者行遷移的。
然後更新test表,並重新dump出來：
SQL> update test set a='test',b='test',c='test',d='test',e='test' where x=1;
1 row updated.
SQL> commit;
Commit complete.
此時應該有行遷移/行連結產生了。
SQL> alter system dump datafile 23 block 34;
System altered.
在udump目錄下檢視trace檔案的內容如下：
Start dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
buffer tsn: 34 rdba: 0x05c00022 (23/34)
scn: 0x0000.0139442b seq: 0x01 flg: 0x02 tail: 0x442b0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x05c00022
Object id on Block? Y
seg/obj: 0x3ccd csc: 0x00.1394429 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.02e.00000ad7 0x00800036.03de.18 C--- 0 scn 0x0000.013943f3
0x02 0x0004.002.00000ae0 0x0080003b.0441.11 --U- 1 fsc 0x0000.0139442b
data_block_dump,data header at 0xadb505c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0adb505c
bdba: 0x05c00022
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x178a
avsp=0x177c
tosp=0x177c
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x178a
block_row_dump:
tab 0, row 0, @0x178a
tl: 2064 fb: --H-F--N lb: 0x2 cc: 3
nrid: 0x05c00023.0
col 0: [ 2] c1 02
col 1: [2000]
74 65 73 74 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
…………
col 2: [48]
74 65 73 74 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
我們不難看出，nrid出現了值，指向了下一個row id,證明剛剛的update操作使這行記錄產生了行連結或者行?
二、行遷移/行連結的檢測
透過前面的介紹我們知道，行連結主要是由於資料庫的db_block_size不夠大，對於一些大的欄位沒法在
一個block中儲存下，從而產生了行連結。對於行連結我們除了增大db_block_size之外沒有別的任何辦法去避免，
但是因為資料庫建立後db_block_size是不可改變的（在9i之前），對於Oracle9i的資料庫我們可以對不同的表空間
指定不同的db_block_size,因此行連結的產生幾乎是不可避免的，也沒有太多可以調整的地方。行遷移則主要是由於
更新表的時候，由於表的pctfree引數設定太小，導致block中沒有足夠的空間去容納更新後的記錄，從而產生了行遷移。
對於行遷移來說就非常有調整的必要了，因為這個是可以調整和控制清除的。
如何檢測資料庫中存在有了行遷移和行連結呢？我們可以利用Oracle資料庫自身提供的指令碼utlchain.sql
（在$ORACLE_HOME/rdbms/admin目錄下）生成chained_rows表，然後利用
ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows命令逐個分析表，將分析的結果存入chained_rows表中。從utlchain.sql指令碼中我們看到 chained_rows的建表指令碼，對於分割槽表，cluster表都是適用的。然後可以使用拼湊語句的辦法生成分析所需要的表的指令碼，並執行指令碼將具體 的分析資料放入Chained_rows表中，例如下面是分析一個使用者下所有表的指令碼：
SPOOL list_migation_rows.sql
SET ECHO OFF
SET HEADING OFF
SELECT 'ANALYZE TABLE ' || table_name ||
' LIST CHAINED ROWS INTO chained_rows;' FROM user_tables;
SPOOL OFF
然後查詢chained_rows表，可以具體檢視某張表上有多少的行連結和行遷移。
SELECT table_name, count(*) from chained_rows GROUP BY table_name;
當然，也可以查詢v$sysstat檢視中的’table fetch continued row’列得到當前的行連結和行遷移數量。
SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';
可以使用如下的指令碼來直接查詢存在有行連結和行遷移的表，自動完成所有的分析和統計。
accept owner prompt " Enter the schema name
to check for Row Chaining (RETURN for All): "
prompt
prompt
accept table prompt " Enter the table name
to check (RETURN for All tables owned by &owner): "
prompt
prompt
set head off serverout on term on feed off veri off echo off
!clear
prompt
declare
v_owner varchar2(30);
v_table varchar2(30);
v_chains number;
v_rows number;
v_count number := 0;
sql_stmt varchar2(100);
dynamicCursor INTEGER;
dummy INTEGER;
cursor chains is
select count(*) from chained_rows;
cursor analyze is
select owner, table_name
from sys.dba_tables
where owner like upper('%&owner%')
and table_name like upper('%&table%')
order by table_name;
begin
dbms_output.enable(64000);
open analyze;
fetch analyze into v_owner, v_table;
while analyze%FOUND loop
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'analyze table '||v_owner
||'.'||v_table||' list chained rows into chained_rows';
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dummy := dbms_sql.execute(dynamicCursor);
dbms_sql.close_cursor(dynamicCursor);
open chains;
fetch chains into v_chains;
if (v_chains != 0) then
if (v_count = 0) then
dbms_output.put_line(CHR(9)||CHR(9)||CHR(9)
||'<<<<< Chained Rows Found >>>>>');
v_count := 1;
end if;
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'Select count(*) v_rows'||' From '||v_owner||'.'||v_table;
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dbms_sql.DEFINE_COLUMN(dynamicCursor, 1, v_rows);
dummy := dbms_sql.execute(dynamicCursor);
dummy := dbms_sql.fetch_rows(dynamicCursor);
dbms_sql.COLUMN_VALUE(dynamicCursor, 1, v_rows);
dbms_sql.close_cursor(dynamicCursor);
dbms_output.put_line(v_owner||'.'||v_table);
dbms_output.put_line(CHR(9)||'---> Has '
||v_chains||' Chained Rows and '||v_rows||' Num_Rows in it!');
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'truncate table chained_rows';
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dummy := dbms_sql.execute(dynamicCursor);
dbms_sql.close_cursor(dynamicCursor);
v_chains := 0;
end if;
close chains;
fetch analyze into v_owner, v_table;
end loop;
if (v_count = 0) then
dbms_output.put_line('No Chained Rows found
in the '||v_owner||' owned Tables!');
end if;
close analyze;
end;
/
set feed on head on
prompt
三、行遷移和行連結的清除
由於對於行連結來說只能增大db_block_size來清除，而db_block_size在建立了資料庫後又是不能改變了的，
所以這裡對行連結的清除不做過多的敘述了，主要是針對行遷移來談談在實際的生產系統中如何去清除。
對於行遷移的清除，一般來說分為兩個步驟：第一步，控制住行遷移的增長，使其不在增多；
第二步，清除掉以前存在的行遷移。
眾所周知，行遷移產生的主要原因是因為表上的pctfree引數設定過小導致的，而要實現第一步控制住行遷移的增長，
就必須設定好一個正確合適的pctfree引數，否則即使清除了當前的行遷移後馬上又會產生很多新的行遷移。
當然，這個引數也不是越大越好的，如果pctfree設定的過大，會導致資料塊的利用率低，造成空間的大量浪費，
因此必須設定一個合理的pctfree引數。如何去確定一個表上合理的pctfree引數呢，一般來說有兩種方法。
第一種是定量的的設定方法，就是利用公式來設定pctfree的大小。
先使用ANALYZE TABLE table_name ESTIMATE STATISTICS命令來分析要修改pctfree的表,
然後檢視user_tables中的AVG_ROW_LEN列值，得到一個平均行長AVG_ROW_LEN1，然後大量的對錶操作之後，
再次使用上述命令分析表，得到第二個平均行長AVG_ROW_LEN2，
然後運用公式100 * （AVG_ROW_LEN2-AVG_ROW_LEN1）/（AVG_ROW_LEN2-AVG_ROW_LEN1 + 原始的AVG_ROW_LEN）
得出的結果就是定量計算出來的一個合適的pctfree的值。這種方法因為是定量計算出來的，可能不一定會很準確，
而且因為要分析表，所以對於使用RBO執行計劃的系統不是很適用。例如：avg_row_len_1 = 60，avg_row_len_2 = 70，
則平均修改量為 10，PCTFREE 應調整為 100 * 10 /（10 + 60）= 16.7% 。
第二種是差分微調的方法，先查詢到當前表的pctfree的值，然後監控和調整pctfree引數，
每次增加一點pctfree的大小，每次增加的比例不要超過5個百分點，
然後使用ANALYZE TABLE TABLE_NAME LIST CHAINED ROWS INTO chained_rows命令分析每次所有的行遷移和行連結的增長情況，
對於不同的表採取不同的增長比例，對於行遷移增長的比較快的表pctfree值就增加的多點，對於增長慢的表就增加的少點，
直到表的行遷移基本保持不增長了為止。但是注意不要把pctfree調的過大，一般在40%以下就可以了，
否則會造成空間的很大浪費和增加資料庫訪問的IO。
使用上述的方法控制住了當前表的行遷移的增長之後，就可以開始清除之前表上存在的行遷移了。
是否清除掉行遷移，關係到系統的效能是否能夠有很大的提高。因此，對於以前存在的行遷移是一定而且必須要清除掉的。
清除掉已經存在的行遷移有很多方法，但是並不是所有的方法都能適用所有的情況，例如表中的記錄數多少，表上的關聯多少、
表上行遷移的數量多少等等這些因素都會是成為制約你使用什麼方法清除的條件，因此，根據表的特點和具體情況的不同
我們應該採用不同的方法去清除行遷移。下面我將逐一介紹各種清除行遷移的方法以及它們各自適用的不同情況。
方法一：傳統的清除行遷移的方法
具體步驟如下：
1． 執行$ORACLE_HOME/rdbms/admin目錄下的utlchain.sql指令碼建立chained_rows表。
@$ORACLE_HOME/rdbms/admin/utlchain.sql
2． 將存在有行遷移的表（用table_name代替）中的產生行遷移的行的rowid放入到chained_rows表中。
ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows;
3． 將表中的行遷移的row id放入臨時表中儲存。
CREATE TABLE table_name_temp AS
SELECT * FROM table_name
WHERE rowid IN
(SELECT head_rowid FROM chained_rows
WHERE table_name = 'table_name');
4． 刪除原來表中存在的行遷移的記錄行。
DELETE table_name
WHERE rowid IN
(SELECT head_rowid
FROM chained_rows
WHERE table_name = 'table_name');
5． 從臨時表中取出並重新插入那些被刪除了的資料到原來的表中，並刪除臨時表。
INSERT INTO table_name SELECT * FROM table_name_temp;
DROP TABLE table_name_temp;
對於這種傳統的清除RM的方法，優點是執行起來過程比較簡單，容易實現。但是這種演算法的缺陷是沒有考慮到表關聯的情況，
在大多數資料庫中很多表都是和別的表之間有表關聯的，有外來鍵的限制，這樣就造成在步驟3中根本無法delete掉存在有
行遷移的記錄行，所以這種方法能夠適用的表的範圍是有限的，只能適用於表上無任何外來鍵關聯的表。由於這種方法在插
入和刪除資料的時候都沒有disable掉索引，這樣導致主要消耗時間是在刪除和插入時維持索引樹的均衡上了，這個對於如果
記錄數不多的情況時間上還比較短，但是如果對於記錄數很多的表這個所消耗的時間就不是能夠接受的了。顯然，這種方法在
處理大資料量的表的時候顯然是不可取的。
以下是一個具體在生產資料庫上清除行遷移的例子，在這之前已經調整過表的pctfree引數至一個合適的值了：
SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sql
Table created.
SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL>SELECT count(*) from chained_rows；
TABLE_NAME COUNT(*)
CUSTOMER 21306
1 rows selected.
檢視在CUSTOMER表上存在的限制:
SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,
TABLE_NAME from USER_CONSTRAINTS where TABLE_NAME='CUSTOMER';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - --
PK_CUSTOMER1 P CUSTOMER
SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,
TABLE_NAME from USER_CONSTRAINTS
where R_CONSTRAINT_NAME='PK_CUSTOMER1';
no rows selected
SQL> CREATE TABLE CUSTOMER_temp AS
SELECT * FROM CUSTOMER WHERE rowid IN
(SELECT head_rowid FROM chained_rows
WHERE table_name = 'CUSTOMER');
Table created.
SQL>select count(*) from CUSTOMER;
COUNT(*)
----------
338299
SQL> DELETE CUSTOMER WHERE rowid IN
(SELECT head_rowid
FROM chained_rows
WHERE table_name = 'CUSTOMER');
21306 rows deleted.
SQL> INSERT INTO CUSTOMER SELECT * FROM CUSTOMER_temp;
21306 rows created.
SQL> DROP TABLE CUSTOMER_temp;
Table dropped.
SQL> commit;
Commit complete.
SQL> select count(*) from CUSTOMER;
COUNT(*)
----------
338299
SQL> truncate table chained_rows;
Table truncated.
SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows;
COUNT(*)
----------
0
以上整個清除兩萬多行的行遷移過程在三分鐘左右，而且全部都在聯機的狀態下完成，基本上不會對業務有什麼影響，
唯一就是在要清除行遷移的表上不能有對外來鍵的限制，否則就不能採用這個方法去清除了。
source:http://blog.itpub.net/178357/viewspace-911323/
在實際的工作中我們經常會碰到一些Oracle資料庫效能較低的問題，當然，
引起Oracle資料庫效能較低的原因是多方面的，Row Migration (行遷移) & Row Chaining (行連結)
就是其中我們可以儘量避免的引起Oracle資料庫效能低下的潛在問題。透過合理的診斷行遷移/行連結，
我們可以較大幅度上提高Oracle資料庫的效能。
那究竟什麼是行遷移/行連結呢，先讓我們從Oracle的block開始談起。
作業系統的最小讀寫操作單元是作業系統的block，所以當建立一個Oracle資料庫的時候我們應該
講資料庫的block size設定成為作業系統的block size的整數倍，Oracle block是Oracle資料庫中讀寫操作的最小單元，
Oracle9i之前的Oracle資料庫版本中Oracle block一旦在建立資料庫的時候被設定後就沒法再更改。
為了在建立資料庫之前確定一個合理的Oracle block的大小，我們需要考慮一些因素，例如資料庫本身
的大小以及併發事務的數量等。使用一個合適的Oracle block大小對於資料庫的調優是非常重要的。
一個Oracle block由三個部分組成，分別是資料塊頭、自由空間、實際資料三部份組成。
資料塊頭：主要包含有資料塊地址的一些基本資訊和段的型別，以及表和包含有資料的實際行的地址。
自由空間：是指可以為以後的更新和插入操作分配的空間，大小由PCTFREE和PCTUSED兩個引數影響。
實際資料：是指在行記憶體儲的實際資料。
當建立或者更改任何表和索引的時候，Oracle在空間控制方面使用兩個儲存引數：
PCTFREE:為將來更新已經存在的資料預留空間的百分比。
PCTUSED:用於為插入一新行資料的最小空間的百分比。這個值決定了塊的可用狀態。可用的塊時可以執行插入的塊，
不可用狀態的塊只能執行刪除和修改，可用狀態的塊被放在freelist中。
當表中一行的資料不能在一個資料block中放入的時候，這個時候就會發生兩種情況，一種是行連結，另外一種就是行遷移了。
行連結產生在第一次插入資料的時候如果一個block不能存放一行記錄的情況下。這種情況下，
Oracle將使用連結一個或者多個在這個段中保留的block儲存這一行記錄，行連結比較容易發生在比較大的行上，
例如行上有LONG、LONG RAW、LOB等資料型別的欄位，這種時候行連結是不可避免的會產生的。
當一行記錄初始插入的時候事可以儲存在一個block中的，由於更新操作導致行長增加了，
而block的自由空間已經完全滿了，這個時候就產生了行遷移。在這種情況下，Oracle將會遷移整行資料到一
個新的block中（假設一個block中可以儲存下整行資料），Oracle會保留被遷移行的原始指標指向新的存放行資料的block，
這就意味著被遷移行的ROW ID是不會改變的。
當發生了行遷移或者行連結，對這行資料操作的效能就會降低，因為Oracle必須要掃描更多的block來獲得這行的資訊。
下面舉例來具體說明行遷移/行連結的產生過程。
先建立一個pctfree為20和pctused為50的測試表：
create table test（
col1 char(20),
col2 number)
storage (
pctfree 20
pctused 50);
當插入一條記錄的時候，Oracle會在free list中先去尋找一個自由的塊，並且將資料插入到這個自由塊中。
而在free list中存在的自由的塊是由pctfree值決定的。初始的空塊都是在free list中的，直到塊中的自由空間
達到pctfree的值，此塊就會從free list中移走，而當此塊中的使用空間低於pctused的時候，此塊又被重新放到free list中。
Oracle使用free list機制可以大大的提高效能，對於每次的插入操作，Oracle只需要查詢free list就可以了，
而不是去查詢所有的block來尋找自由空間。
具體我們透過下面的一個試驗來檢視行連結和行遷移是如何產生並在資料檔案中體現出來的。先檢視ALLAN這個
表空間的資料檔案號，為了便於測試，我只建立了一個資料檔案。
SQL> select file_id from dba_data_files where tablespace_name='ALLAN';
FILE_ID
----------
23
建立一個測試表test：
SQL> create table test ( x int primary key,
a char(2000), b char(2000), c char(2000),
d char(2000), e char(2000) ) tablespace allan;
Table created.
因為我的資料庫的db_block_size是8K，所以我建立的表有五個欄位，每個佔2000個位元組，這樣一行記錄大約10K,
就能超過一個block的大小了。
然後插入一行記錄，只有一個欄位的：
SQL> insert into test(x) values (1);
1 row created.
SQL> commit;
Commit complete.
查詢這行記錄所在的block，並dump出來：
SQL> select dbms_rowid.rowid_block_number(rowid) from test;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
34
SQL> alter system dump datafile 23 block 34;
System altered.
在udump目錄下檢視trace檔案的內容如下：
Start dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
buffer tsn: 34 rdba: 0x05c00022 (23/34)
scn: 0x0000.013943f3 seq: 0x01 flg: 0x02 tail: 0x43f30601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x05c00022
Object id on Block? Y
seg/obj: 0x3ccd csc: 0x00.13943ef itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.02e.00000ad7 0x00800036.03de.18 --U- 1 fsc 0x0000.013943f3
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0xadb505c
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0adb505c
bdba: 0x05c00022
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f9a
avsp=0x1f83
tosp=0x1f83
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f9a
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 02
end_of_block_dump
End dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
對其中的一些資訊做一些解釋：
Fb:H是指行記錄的頭，L是指行記錄的最後一列，F是指行記錄的第一列。
Cc：列的數量。
Nrid：對於行連結或者行遷移來說的下一個row id的值。
由上面的dump資訊我們可以看出來當前表test是沒有行連結或者行遷移的。
然後更新test表,並重新dump出來：
SQL> update test set a='test',b='test',c='test',d='test',e='test' where x=1;
1 row updated.
SQL> commit;
Commit complete.
此時應該有行遷移/行連結產生了。
SQL> alter system dump datafile 23 block 34;
System altered.
在udump目錄下檢視trace檔案的內容如下：
Start dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
buffer tsn: 34 rdba: 0x05c00022 (23/34)
scn: 0x0000.0139442b seq: 0x01 flg: 0x02 tail: 0x442b0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x05c00022
Object id on Block? Y
seg/obj: 0x3ccd csc: 0x00.1394429 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.02e.00000ad7 0x00800036.03de.18 C--- 0 scn 0x0000.013943f3
0x02 0x0004.002.00000ae0 0x0080003b.0441.11 --U- 1 fsc 0x0000.0139442b
data_block_dump,data header at 0xadb505c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0adb505c
bdba: 0x05c00022
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x178a
avsp=0x177c
tosp=0x177c
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x178a
block_row_dump:
tab 0, row 0, @0x178a
tl: 2064 fb: --H-F--N lb: 0x2 cc: 3
nrid: 0x05c00023.0
col 0: [ 2] c1 02
col 1: [2000]
74 65 73 74 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
…………
col 2: [48]
74 65 73 74 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34
我們不難看出，nrid出現了值，指向了下一個row id,證明剛剛的update操作使這行記錄產生了行連結或者行?
二、行遷移/行連結的檢測
透過前面的介紹我們知道，行連結主要是由於資料庫的db_block_size不夠大，對於一些大的欄位沒法在
一個block中儲存下，從而產生了行連結。對於行連結我們除了增大db_block_size之外沒有別的任何辦法去避免，
但是因為資料庫建立後db_block_size是不可改變的（在9i之前），對於Oracle9i的資料庫我們可以對不同的表空間
指定不同的db_block_size,因此行連結的產生幾乎是不可避免的，也沒有太多可以調整的地方。行遷移則主要是由於
更新表的時候，由於表的pctfree引數設定太小，導致block中沒有足夠的空間去容納更新後的記錄，從而產生了行遷移。
對於行遷移來說就非常有調整的必要了，因為這個是可以調整和控制清除的。
如何檢測資料庫中存在有了行遷移和行連結呢？我們可以利用Oracle資料庫自身提供的指令碼utlchain.sql
（在$ORACLE_HOME/rdbms/admin目錄下）生成chained_rows表，然後利用
ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows命令逐個分析表，將分析的結果存入chained_rows表中。從utlchain.sql指令碼中我們看到 chained_rows的建表指令碼，對於分割槽表，cluster表都是適用的。然後可以使用拼湊語句的辦法生成分析所需要的表的指令碼，並執行指令碼將具體 的分析資料放入Chained_rows表中，例如下面是分析一個使用者下所有表的指令碼：
SPOOL list_migation_rows.sql
SET ECHO OFF
SET HEADING OFF
SELECT 'ANALYZE TABLE ' || table_name ||
' LIST CHAINED ROWS INTO chained_rows;' FROM user_tables;
SPOOL OFF
然後查詢chained_rows表，可以具體檢視某張表上有多少的行連結和行遷移。
SELECT table_name, count(*) from chained_rows GROUP BY table_name;
當然，也可以查詢v$sysstat檢視中的’table fetch continued row’列得到當前的行連結和行遷移數量。
SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';
可以使用如下的指令碼來直接查詢存在有行連結和行遷移的表，自動完成所有的分析和統計。
accept owner prompt " Enter the schema name
to check for Row Chaining (RETURN for All): "
prompt
prompt
accept table prompt " Enter the table name
to check (RETURN for All tables owned by &owner): "
prompt
prompt
set head off serverout on term on feed off veri off echo off
!clear
prompt
declare
v_owner varchar2(30);
v_table varchar2(30);
v_chains number;
v_rows number;
v_count number := 0;
sql_stmt varchar2(100);
dynamicCursor INTEGER;
dummy INTEGER;
cursor chains is
select count(*) from chained_rows;
cursor analyze is
select owner, table_name
from sys.dba_tables
where owner like upper('%&owner%')
and table_name like upper('%&table%')
order by table_name;
begin
dbms_output.enable(64000);
open analyze;
fetch analyze into v_owner, v_table;
while analyze%FOUND loop
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'analyze table '||v_owner
||'.'||v_table||' list chained rows into chained_rows';
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dummy := dbms_sql.execute(dynamicCursor);
dbms_sql.close_cursor(dynamicCursor);
open chains;
fetch chains into v_chains;
if (v_chains != 0) then
if (v_count = 0) then
dbms_output.put_line(CHR(9)||CHR(9)||CHR(9)
||'<<<<< Chained Rows Found >>>>>');
v_count := 1;
end if;
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'Select count(*) v_rows'||' From '||v_owner||'.'||v_table;
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dbms_sql.DEFINE_COLUMN(dynamicCursor, 1, v_rows);
dummy := dbms_sql.execute(dynamicCursor);
dummy := dbms_sql.fetch_rows(dynamicCursor);
dbms_sql.COLUMN_VALUE(dynamicCursor, 1, v_rows);
dbms_sql.close_cursor(dynamicCursor);
dbms_output.put_line(v_owner||'.'||v_table);
dbms_output.put_line(CHR(9)||'---> Has '
||v_chains||' Chained Rows and '||v_rows||' Num_Rows in it!');
dynamicCursor := dbms_sql.open_cursor;
sql_stmt := 'truncate table chained_rows';
dbms_sql.parse(dynamicCursor, sql_stmt, dbms_sql.native);
dummy := dbms_sql.execute(dynamicCursor);
dbms_sql.close_cursor(dynamicCursor);
v_chains := 0;
end if;
close chains;
fetch analyze into v_owner, v_table;
end loop;
if (v_count = 0) then
dbms_output.put_line('No Chained Rows found
in the '||v_owner||' owned Tables!');
end if;
close analyze;
end;
/
set feed on head on
prompt
三、行遷移和行連結的清除
由於對於行連結來說只能增大db_block_size來清除，而db_block_size在建立了資料庫後又是不能改變了的，
所以這裡對行連結的清除不做過多的敘述了，主要是針對行遷移來談談在實際的生產系統中如何去清除。
對於行遷移的清除，一般來說分為兩個步驟：第一步，控制住行遷移的增長，使其不在增多；
第二步，清除掉以前存在的行遷移。
眾所周知，行遷移產生的主要原因是因為表上的pctfree引數設定過小導致的，而要實現第一步控制住行遷移的增長，
就必須設定好一個正確合適的pctfree引數，否則即使清除了當前的行遷移後馬上又會產生很多新的行遷移。
當然，這個引數也不是越大越好的，如果pctfree設定的過大，會導致資料塊的利用率低，造成空間的大量浪費，
因此必須設定一個合理的pctfree引數。如何去確定一個表上合理的pctfree引數呢，一般來說有兩種方法。
第一種是定量的的設定方法，就是利用公式來設定pctfree的大小。
先使用ANALYZE TABLE table_name ESTIMATE STATISTICS命令來分析要修改pctfree的表,
然後檢視user_tables中的AVG_ROW_LEN列值，得到一個平均行長AVG_ROW_LEN1，然後大量的對錶操作之後，
再次使用上述命令分析表，得到第二個平均行長AVG_ROW_LEN2，
然後運用公式100 * （AVG_ROW_LEN2-AVG_ROW_LEN1）/（AVG_ROW_LEN2-AVG_ROW_LEN1 + 原始的AVG_ROW_LEN）
得出的結果就是定量計算出來的一個合適的pctfree的值。這種方法因為是定量計算出來的，可能不一定會很準確，
而且因為要分析表，所以對於使用RBO執行計劃的系統不是很適用。例如：avg_row_len_1 = 60，avg_row_len_2 = 70，
則平均修改量為 10，PCTFREE 應調整為 100 * 10 /（10 + 60）= 16.7% 。
第二種是差分微調的方法，先查詢到當前表的pctfree的值，然後監控和調整pctfree引數，
每次增加一點pctfree的大小，每次增加的比例不要超過5個百分點，
然後使用ANALYZE TABLE TABLE_NAME LIST CHAINED ROWS INTO chained_rows命令分析每次所有的行遷移和行連結的增長情況，
對於不同的表採取不同的增長比例，對於行遷移增長的比較快的表pctfree值就增加的多點，對於增長慢的表就增加的少點，
直到表的行遷移基本保持不增長了為止。但是注意不要把pctfree調的過大，一般在40%以下就可以了，
否則會造成空間的很大浪費和增加資料庫訪問的IO。
使用上述的方法控制住了當前表的行遷移的增長之後，就可以開始清除之前表上存在的行遷移了。
是否清除掉行遷移，關係到系統的效能是否能夠有很大的提高。因此，對於以前存在的行遷移是一定而且必須要清除掉的。
清除掉已經存在的行遷移有很多方法，但是並不是所有的方法都能適用所有的情況，例如表中的記錄數多少，表上的關聯多少、
表上行遷移的數量多少等等這些因素都會是成為制約你使用什麼方法清除的條件，因此，根據表的特點和具體情況的不同
我們應該採用不同的方法去清除行遷移。下面我將逐一介紹各種清除行遷移的方法以及它們各自適用的不同情況。
方法一：傳統的清除行遷移的方法
具體步驟如下：
1． 執行$ORACLE_HOME/rdbms/admin目錄下的utlchain.sql指令碼建立chained_rows表。
@$ORACLE_HOME/rdbms/admin/utlchain.sql
2． 將存在有行遷移的表（用table_name代替）中的產生行遷移的行的rowid放入到chained_rows表中。
ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows;
3． 將表中的行遷移的row id放入臨時表中儲存。
CREATE TABLE table_name_temp AS
SELECT * FROM table_name
WHERE rowid IN
(SELECT head_rowid FROM chained_rows
WHERE table_name = 'table_name');
4． 刪除原來表中存在的行遷移的記錄行。
DELETE table_name
WHERE rowid IN
(SELECT head_rowid
FROM chained_rows
WHERE table_name = 'table_name');
5． 從臨時表中取出並重新插入那些被刪除了的資料到原來的表中，並刪除臨時表。
INSERT INTO table_name SELECT * FROM table_name_temp;
DROP TABLE table_name_temp;
對於這種傳統的清除RM的方法，優點是執行起來過程比較簡單，容易實現。但是這種演算法的缺陷是沒有考慮到表關聯的情況，
在大多數資料庫中很多表都是和別的表之間有表關聯的，有外來鍵的限制，這樣就造成在步驟3中根本無法delete掉存在有
行遷移的記錄行，所以這種方法能夠適用的表的範圍是有限的，只能適用於表上無任何外來鍵關聯的表。由於這種方法在插
入和刪除資料的時候都沒有disable掉索引，這樣導致主要消耗時間是在刪除和插入時維持索引樹的均衡上了，這個對於如果
記錄數不多的情況時間上還比較短，但是如果對於記錄數很多的表這個所消耗的時間就不是能夠接受的了。顯然，這種方法在
處理大資料量的表的時候顯然是不可取的。
以下是一個具體在生產資料庫上清除行遷移的例子，在這之前已經調整過表的pctfree引數至一個合適的值了：
SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sql
Table created.
SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL>SELECT count(*) from chained_rows；
TABLE_NAME COUNT(*)
CUSTOMER 21306
1 rows selected.
檢視在CUSTOMER表上存在的限制:
SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,
TABLE_NAME from USER_CONSTRAINTS where TABLE_NAME='CUSTOMER';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - --
PK_CUSTOMER1 P CUSTOMER
SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,
TABLE_NAME from USER_CONSTRAINTS
where R_CONSTRAINT_NAME='PK_CUSTOMER1';
no rows selected
SQL> CREATE TABLE CUSTOMER_temp AS
SELECT * FROM CUSTOMER WHERE rowid IN
(SELECT head_rowid FROM chained_rows
WHERE table_name = 'CUSTOMER');
Table created.
SQL>select count(*) from CUSTOMER;
COUNT(*)
----------
338299
SQL> DELETE CUSTOMER WHERE rowid IN
(SELECT head_rowid
FROM chained_rows
WHERE table_name = 'CUSTOMER');
21306 rows deleted.
SQL> INSERT INTO CUSTOMER SELECT * FROM CUSTOMER_temp;
21306 rows created.
SQL> DROP TABLE CUSTOMER_temp;
Table dropped.
SQL> commit;
Commit complete.
SQL> select count(*) from CUSTOMER;
COUNT(*)
----------
338299
SQL> truncate table chained_rows;
Table truncated.
SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows;
COUNT(*)
----------
0
以上整個清除兩萬多行的行遷移過程在三分鐘左右，而且全部都在聯機的狀態下完成，基本上不會對業務有什麼影響，
唯一就是在要清除行遷移的表上不能有對外來鍵的限制，否則就不能採用這個方法去清除了。
source:http://blog.itpub.net/178357/viewspace-911323/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27036311/viewspace-1314069/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 模擬Oracle行遷移和行連結Oracle
- Oracle 行遷移 & 行連結的檢測與消除Oracle
- [20190120]行連結遷移與dml.txt
- [20180402]行連結行遷移與ITL槽6.txt
- Oracle資料庫中資料行遷移與行連結Oracle資料庫
- 遷移執行緒migration執行緒
- 高效能圖表LightningChart JS 遷移指南介紹GCJS
- 深度學習之遷移學習介紹與使用深度學習遷移學習
- 20202.9.29課堂筆記(Sqoop介紹及資料遷移)筆記OOP
- 執行緒池介紹執行緒
- JavaScript資料結構之連結串列--介紹JavaScript資料結構
- [20180327]行遷移與ITL浪費.txt
- iOS 多執行緒介紹iOS執行緒
- 使用Conda Pack進行環境打包遷移
- [20230425]CBO cost與行遷移關係.txt
- 杉巖資料銀行Documentum遷移方案
- 使用Mobilenet和Keras進行遷移學習！Keras遷移學習
- 關於伺服器資料遷移，介紹在伺服器資料遷移計劃中的7個步驟伺服器
- 進行Linux效能監控的命令介紹Linux
- MyBatis執行流程的各階段介紹MyBatis
- Linux 主要的發行系統版本介紹Linux
- 執行緒本地ThreadLocal的介紹與使用！執行緒thread
- 多執行緒系列(十七) -執行緒組介紹執行緒
- Android執行緒池使用介紹Android執行緒
- JuiceFS 直連 NFS 新功能介紹，賦能 NAS 進行 AI 訓練UINFSAI
- “遷移策略+新容器執行時”應對有狀態應用的冷熱遷移挑戰
- 最新生成新浪短連結API介面介紹API
- 1.5 使用nvicat和kettle進行全量遷移
- EF Core 小技巧：遷移已經應用到資料庫，如何進行遷移回退操作？資料庫
- python關於執行緒的一點介紹Python執行緒
- 簡單介紹python中的單向連結串列實現Python
- Nexus.js介紹：一個多執行緒的JavaScript執行庫JS執行緒JavaScript
- 晶片行業管理系統功能介紹晶片行業
- MySQL DDL執行方式-Online DDL介紹MySql
- 執行緒介紹及建立方式執行緒
- 連載一：Oracle遷移文件大全Oracle
- 連載二：Oracle遷移文章大全Oracle
- 最新生成新浪短連結網址介面介紹
- Kubernetes怎麼進行NFS動態儲存遷移NFS