一、行遷移/行鏈接的介紹 在實(shí)際的工作中我們經(jīng)常會碰到一些Oracle數(shù)據(jù)庫性能較低的問題，當(dāng)然，引起Oracle數(shù)據(jù)庫性能較低的原因是多方面的，我們能夠通過一些正確的設(shè)計和診斷來盡量的避免一些Oracle數(shù)據(jù)庫性能不好，Row Migration (行遷移) & Row Chaining (行鏈接)就是其中我們可以盡量避免的引起Oracle數(shù)據(jù)庫性能低下的潛在問題。通過合理的診斷行遷移/行鏈接，我們可以較大幅度上提高Oracle數(shù)據(jù)庫的性能。 那究竟什么是行遷移/行鏈接呢，先讓我們從Oracle的block開始談起。 操作系統(tǒng)的最小讀寫操作單元是操作系統(tǒng)的block，所以當(dāng)創(chuàng)建一個Oracle數(shù)據(jù)庫的時候我們應(yīng)該講數(shù)據(jù)庫的block size設(shè)置成為操作系統(tǒng)的block size的整數(shù)倍，Oracle block是Oracle數(shù)據(jù)庫中讀寫操作的最小單元，Oracle9i之前的Oracle數(shù)據(jù)庫版本中Oracle block一旦在創(chuàng)建數(shù)據(jù)庫的時候被設(shè)定后就沒法再更改。為了在創(chuàng)建數(shù)據(jù)庫之前確定一個合理的Oracle block的大小，我們需要考慮一些因素，例如數(shù)據(jù)庫本身的大小以及并發(fā)事務(wù)的數(shù)量等。使用一個合適的Oracle block大小對于數(shù)據(jù)庫的調(diào)優(yōu)是非常重要的。 一個Oracle block由三個部分組成，分別是數(shù)據(jù)塊頭、自由空間、實(shí)際數(shù)據(jù)三部份組成。 數(shù)據(jù)塊頭：主要包含有數(shù)據(jù)塊地址的一些基本信息和段的類型，以及表和包含有數(shù)據(jù)的實(shí)際行的地址。 自由空間：是指可以為以后的更新和插入操作分配的空間，大小由PCTFREE和PCTUSED兩個參數(shù)影響。 實(shí)際數(shù)據(jù)：是指在行內(nèi)存儲的實(shí)際數(shù)據(jù)。 當(dāng)創(chuàng)建或者更改任何表和索引的時候，Oracle在空間控制方面使用兩個存儲參數(shù)： PCTFREE:為將來更新已經(jīng)存在的數(shù)據(jù)預(yù)留空間的百分比。 PCTUSED:用于為插入一新行數(shù)據(jù)的最小空間的百分比。這個值決定了塊的可用狀態(tài)?？捎玫膲K時可以執(zhí)行插入的塊，不可用狀態(tài)的塊只能執(zhí)行刪除和修改，可用狀態(tài)的塊被放在freelist中。 當(dāng)表中一行的數(shù)據(jù)不能在一個數(shù)據(jù)block中放入的時候，這個時候就會發(fā)生兩種情況，一種是行鏈接，另外一種就是行遷移了。 行鏈接產(chǎn)生在第一次插入數(shù)據(jù)的時候假如一個block不能存放一行記錄的情況下。這種情況下，Oracle將使用鏈接一個或者多個在這個段中保留的block存儲這一行記錄，行鏈接比較輕易發(fā)生在比較大的行上，例如行上有LONG、LONG RAW、LOB等數(shù)據(jù)類型的字段，這種時候行鏈接是不可避免的會產(chǎn)生的。 當(dāng)一行記錄初始插入的時候事可以存儲在一個block中的，由于更新操作導(dǎo)致行長增加了，而block的自由空間已經(jīng)完全滿了，這個時候就產(chǎn)生了行遷移。在這種情況下，Oracle將會遷移整行數(shù)據(jù)到一個新的block中（假設(shè)一個block中可以存儲下整行數(shù)據(jù)），Oracle會保留被遷移行的原始指針指向新的存放行數(shù)據(jù)的block，這就意味著被遷移行的ROW ID是不會改變的。 當(dāng)發(fā)生了行遷移或者行鏈接，對這行數(shù)據(jù)操作的性能就會降低，因?yàn)镺racle必須要掃描更多的block來獲得這行的信息。 下面舉例來具體說明行遷移/行鏈接的產(chǎn)生過程。 先創(chuàng)建一個pctfree為20和pctused為50的測試表： create table test（col1 char(20),col2 number)storage (pctfree 20pctused 50);當(dāng)插入一條記錄的時候，Oracle會在free list中先去尋找一個自由的塊，并且將數(shù)據(jù)插入到這個自由塊中。而在free list中存在的自由的塊是由pctfree值決定的。初始的空塊都是在free list中的，直到塊中的自由空間達(dá)到pctfree的值，此塊就會從free list中移走，而當(dāng)此塊中的使用空間低于pctused的時候，此塊又被重新放到free list中。 Oracle使用free list機(jī)制可以大大的提高性能，對于每次的插入操作，Oracle只需要查找free list就可以了，而不是去查找所有的block來尋找自由空間。 具體我們通過下面的一個試驗(yàn)來查看行鏈接和行遷移是如何產(chǎn)生并在數(shù)據(jù)文件中體現(xiàn)出來的。先查看ALLAN這個表空間的數(shù)據(jù)文件號，為了便于測試，我只建立了一個數(shù)據(jù)文件。 SQL> select file_id from dba_data_files where tablespace_name='ALLAN';FILE_ID----------23創(chuàng)建一個測試表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.因?yàn)槲业臄?shù)據(jù)庫的db_block_size是8K，所以我創(chuàng)建的表有五個字段，每個占2000個字節(jié)，這樣一行記錄大約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)34SQL> alter system dump datafile 23 block 34;System altered.在udump目錄下查看trace文件的內(nèi)容如下： Start dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34buffer tsn: 34 rdba: 0x05c00022 (23/34)scn: 0x0000.013943f3 seq: 0x01 flg: 0x02 tail: 0x43f30601frmt: 0x02 chkval: 0x0000 type: 0x06=trans dataBlock header dump: 0x05c00022Object id on Block? Yseg/obj: 0x3ccd csc: 0x00.13943ef itc: 2 flg: O typ: 1 - DATAfsl: 0 fnx: 0x0 ver: 0x01Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.02e.00000ad7 0x00800036.03de.18 --U- 1 fsc 0x0000.013943f30x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000data_block_dump,data header at 0xadb505ctsiz: 0x1fa0hsiz: 0x14pbl: 0x0adb505cbdba: 0x05c0002276543210flag=--------ntab=1nrow=1frre=-1fsbo=0x14fseo=0x1f9aavsp=0x1f83tosp=0x1f830xe:pti[0] nrow=1 offs=00x12:pri[0] offs=0x1f9ablock_row_dump:tab 0, row 0, @0x1f9atl: 6 fb: --H-FL-- lb: 0x1 cc: 1col 0: [ 2] c1 02end_of_block_dumpEnd dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34對其中的一些信息做一些解釋： Fb:H是指行記錄的頭，L是指行記錄的最后一列，F(xiàn)是指行記錄的第一列。 Cc：列的數(shù)量。 Nrid：對于行鏈接或者行遷移來說的下一個row id的值。 由上面的dump信息我們可以看出來當(dāng)前表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.此時應(yīng)該有行遷移/行鏈接產(chǎn)生了。 SQL> alter system dump datafile 23 block 34;System altered.在udump目錄下查看trace文件的內(nèi)容如下： Start dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34buffer tsn: 34 rdba: 0x05c00022 (23/34)scn: 0x0000.0139442b seq: 0x01 flg: 0x02 tail: 0x442b0601frmt: 0x02 chkval: 0x0000 type: 0x06=trans dataBlock header dump: 0x05c00022Object id on Block? Yseg/obj: 0x3ccd csc: 0x00.1394429 itc: 2 flg: - typ: 1 - DATAfsl: 0 fnx: 0x0 ver: 0x01Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.02e.00000ad7 0x00800036.03de.18 C--- 0 scn 0x0000.013943f30x02 0x0004.002.00000ae0 0x0080003b.0441.11 --U- 1 fsc 0x0000.0139442bdata_block_dump,data header at 0xadb505c===============tsiz: 0x1fa0hsiz: 0x14pbl: 0x0adb505cbdba: 0x05c0002276543210flag=--------ntab=1nrow=1frre=-1fsbo=0x14fseo=0x178aavsp=0x177ctosp=0x177c0xe:pti[0] nrow=1 offs=00x12:pri[0] offs=0x178ablock_row_dump:tab 0, row 0, @0x178atl: 2064 fb: --H-F--N lb: 0x2 cc: 3nrid: 0x05c00023.0col 0: [ 2] c1 02col 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 2020 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 2020 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20end_of_block_dumpEnd dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34我們不難看出，nrid出現(xiàn)了值，指向了下一個row id,證實(shí)剛剛的update操作使這行記錄產(chǎn)生了行鏈接或者行? 二、行遷移/行鏈接的檢測 通過前面的介紹我們知道，行鏈接主要是由于數(shù)據(jù)庫的db_block_size不夠大，對于一些大的字段沒法在一個block中存儲下，從而產(chǎn)生了行鏈接。對于行鏈接我們除了增大db_block_size之外沒有別的任何辦法去避免，但是因?yàn)閿?shù)據(jù)庫建立后db_block_size是不可改變的（在9i之前），對于Oracle9i的數(shù)據(jù)庫我們可以對不同的表空間指定不同的db_block_size,因此行鏈接的產(chǎn)生幾乎是不可避免的，也沒有太多可以調(diào)整的地方。行遷移則主要是由于更新表的時候，由于表的pctfree參數(shù)設(shè)置太小，導(dǎo)致block中沒有足夠的空間去容納更新后的記錄，從而產(chǎn)生了行遷移。對于行遷移來說就非常有調(diào)整的必要了，因?yàn)檫@個是可以調(diào)整和控制清除的。 如何檢測數(shù)據(jù)庫中存在有了行遷移和行鏈接呢？我們可以利用Oracle數(shù)據(jù)庫自身提供的腳本utlchain.sql（在$ORACLE_HOME/rdbms/admin目錄下）生成chained_rows表，然后利用ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows命令逐個分析表，將分析的結(jié)果存入chained_rows表中。從utlchain.sql腳本中我們看到chained_rows的建表腳本，對于分區(qū)表，cluster表都是適用的。然后可以使用拼湊語句的辦法生成分析所需要的表的腳本，并執(zhí)行腳本將具體的分析數(shù)據(jù)放入Chained_rows表中，例如下面是分析一個用戶下所有表的腳本： SPOOL list_migation_rows.sqlSET ECHO OFFSET HEADING OFFSELECT '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;當(dāng)然，也可以查詢v$sysstat視圖中的’table fetch continued row’列得到當(dāng)前的行鏈接和行遷移數(shù)量。 SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';可以使用如下的腳本來直接查找存在有行鏈接和行遷移的表，自動完成所有的分析和統(tǒng)計。 accept owner prompt ' Enter the schema name to check for Row Chaining (RETURN for All): 'promptpromptaccept table prompt ' Enter the table name to check (RETURN for All tables owned by &owner): 'promptpromptset head off serverout on term on feed off veri off echo off!clearprompt declarev_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 isselect count(*) from chained_rows;cursor analyze isselect owner, table_namefrom sys.dba_tables where owner like upper('%&owner%')and table_name like upper('%&table%')order by table_name;begindbms_output.enable(64000);open analyze;fetch analyze into v_owner, v_table;while analyze%FOUND loopdynamicCursor := 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) thenif (v_count = 0) thendbms_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) thendbms_output.put_line('No Chained Rows found in the 'v_owner' owned Tables!');end if;close analyze;end;/set feed on head onprompt三、行遷移和行鏈接的清除 由于對于行鏈接來說只能增大db_block_size來清除，而db_block_size在創(chuàng)建了數(shù)據(jù)庫后又是不能改變了的，所以這里對行鏈接的清除不做過多的敘述了，主要是針對行遷移來談?wù)勗趯?shí)際的生產(chǎn)系統(tǒng)中如何去清除。 對于行遷移的清除，一般來說分為兩個步驟：第一步，控制住行遷移的增長，使其不在增多；第二步，清除掉以前存在的行遷移。 眾所周知，行遷移產(chǎn)生的主要原因是因?yàn)楸砩系膒ctfree參數(shù)設(shè)置過小導(dǎo)致的，而要實(shí)現(xiàn)第一步控制住行遷移的增長，就必須設(shè)置好一個正確合適的pctfree參數(shù)，否則即使清除了當(dāng)前的行遷移后馬上又會產(chǎn)生很多新的行遷移。當(dāng)然，這個參數(shù)也不是越大越好的，假如pctfree設(shè)置的過大，會導(dǎo)致數(shù)據(jù)塊的利用率低，造成空間的大量浪費(fèi)，因此必須設(shè)置一個合理的pctfree參數(shù)。如何去確定一個表上合理的pctfree參數(shù)呢，一般來說有兩種方法。 第一種是定量的的設(shè)定方法，就是利用公式來設(shè)定pctfree的大小。先使用ANALYZE TABLE table_name ESTIMATE STATISTICS命令來分析要修改pctfree的表,然后查看user_tables中的AVG_ROW_LEN列值，得到一個平均行長AVG_ROW_LEN1，然后大量的對表操作之后，再次使用上述命令分析表，得到第二個平均行長AVG_ROW_LEN2，然后運(yùn)用公式100 * （AVG_ROW_LEN2-AVG_ROW_LEN1）/（AVG_ROW_LEN2-AVG_ROW_LEN1 + 原始的AVG_ROW_LEN）得出的結(jié)果就是定量計算出來的一個合適的pctfree的值。這種方法因?yàn)槭嵌坑嬎愠鰜淼?，可能不一定會很?zhǔn)確，而且因?yàn)橐治霰?，所以對于使用RBO執(zhí)行計劃的系統(tǒng)不是很適用。例如：avg_row_len_1 = 60，avg_row_len_2 = 70，則平均修改量為 10，PCTFREE 應(yīng)調(diào)整為 100 * 10 /（10 + 60）= 16.7% 。 第二種是差分微調(diào)的方法，先查詢到當(dāng)前表的pctfree的值，然后監(jiān)控和調(diào)整pctfree參數(shù)，每次增加一點(diǎn)pctfree的大小，每次增加的比例不要超過5個百分點(diǎn)，然后使用ANALYZE TABLE TABLE_NAME LIST CHAINED ROWS INTO chained_rows命令分析每次所有的行遷移和行鏈接的增長情況，對于不同的表采取不同的增長比例，對于行遷移增長的比較快的表pctfree值就增加的多點(diǎn)，對于增長慢的表就增加的少點(diǎn)，直到表的行遷移基本保持不增長了為止。但是注重不要把pctfree調(diào)的過大，一般在40%以下就可以了，否則會造成空間的很大浪費(fèi)和增加數(shù)據(jù)庫訪問的IO。 使用上述的方法控制住了當(dāng)前表的行遷移的增長之后，就可以開始清除之前表上存在的行遷移了。是否清除掉行遷移，關(guān)系到系統(tǒng)的性能是否能夠有很大的提高。因此，對于以前存在的行遷移是一定而且必須要清除掉的。清除掉已經(jīng)存在的行遷移有很多方法，但是并不是所有的方法都能適用所有的情況，例如表中的記錄數(shù)多少，表上的關(guān)聯(lián)多少、表上行遷移的數(shù)量多少等等這些因素都會是成為制約你使用什么方法清除的條件，因此，根據(jù)表的特點(diǎn)和具體情況的不同我們應(yīng)該采用不同的方法去清除行遷移。下面我將逐一介紹各種清除行遷移的方法以及它們各自適用的不同情況。 方法一：傳統(tǒng)的清除行遷移的方法 具體步驟如下： 1． 執(zhí)行$ORACLE_HOME/rdbms/admin目錄下的utlchain.sql腳本創(chuàng)建chained_rows表。 @$ORACLE_HOME/rdbms/admin/utlchain.sql 2． 將存在有行遷移的表（用table_name代替）中的產(chǎn)生行遷移的行的rowid放入到chained_rows表中。 ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows; 3． 將表中的行遷移的row id放入臨時表中保存。 CREATE TABLE table_name_temp ASSELECT * FROM table_nameWHERE rowid IN(SELECT head_rowid FROM chained_rowsWHERE table_name = 'table_name');4． 刪除原來表中存在的行遷移的記錄行。 DELETE table_nameWHERE rowid IN(SELECT head_rowidFROM chained_rowsWHERE table_name = 'table_name');5． 從臨時表中取出并重新插入那些被刪除了的數(shù)據(jù)到原來的表中，并刪除臨時表。 INSERT INTO table_name SELECT * FROM table_name_temp;DROP TABLE table_name_temp;對于這種傳統(tǒng)的清除RM的方法，優(yōu)點(diǎn)是執(zhí)行起來過程比較簡單，輕易實(shí)現(xiàn)。但是這種算法的缺陷是沒有考慮到表關(guān)聯(lián)的情況，在大多數(shù)數(shù)據(jù)庫中很多表都是和別的表之間有表關(guān)聯(lián)的，有外鍵的限制，這樣就造成在步驟3中根本無法delete掉存在有行遷移的記錄行，所以這種方法能夠適用的表的范圍是有限的，只能適用于表上無任何外鍵關(guān)聯(lián)的表。由于這種方法在插入和刪除數(shù)據(jù)的時候都沒有disable掉索引，這樣導(dǎo)致主要消耗時間是在刪除和插入時維持索引樹的均衡上了，這個對于假如記錄數(shù)不多的情況時間上還比較短，但是假如對于記錄數(shù)很多的表這個所消耗的時間就不是能夠接受的了。顯然，這種方法在處理大數(shù)據(jù)量的表的時候顯然是不可取的。 以下是一個具體在生產(chǎn)數(shù)據(jù)庫上清除行遷移的例子，在這之前已經(jīng)調(diào)整過表的pctfree參數(shù)至一個合適的值了： SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sqlTable created.SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;Table analyzed.SQL>SELECT count(*) from chained_rows；TABLE_NAME COUNT(*)CUSTOMER 213061 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 CUSTOMERSQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_CUSTOMER1';no rows selectedSQL> CREATE TABLE CUSTOMER_temp ASSELECT * FROM CUSTOMER WHERE rowid IN(SELECT head_rowid FROM chained_rowsWHERE table_name = 'CUSTOMER'); Table created.SQL>select count(*) from CUSTOMER;COUNT(*)----------338299SQL> DELETE CUSTOMER WHERE rowid IN(SELECT head_rowidFROM chained_rowsWHERE 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(*)----------338299SQL> 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以上整個清除兩萬多行的行遷移過程在三分鐘左右，而且全部都在聯(lián)機(jī)的狀態(tài)下完成，基本上不會對業(yè)務(wù)有什么影響，唯一就是在要清除行遷移的表上不能有對外鍵的限制，否則就不能采用這個方法去清除了。