Oracle表片段起因及解決辦法

跟表片段有關的基礎知識：
什麼是水線(High Water Mark)?
----------------------------
所有的oracle段(segments，在此，為了理解方便，建議把segment作為表的一個同義字) 都有一個在段內容納資料的上限，我們把這個上限稱為"high water mark"或HWM。這個HWM是一個標記，用來說明已經有多少沒有使用的資料區塊分配給這個segment。HWM通常增長的幅度為一次5個資料區塊，原則上HWM只會增大，不會縮小，即使將表中的資料全部刪除，HWM還是為原值，由於這個特點，使HWM很象一個水庫的曆史最高水位，這也就是HWM的原始含義，當然不能說一個水庫沒水了，就說該水庫的曆史最高水位為0。但是如果我們在表上使用了truncate命令，則該表的HWM會被重新置為0。

HWM資料庫的操作有如下影響：
a) 全表掃描通常要讀出直到HWM標記的所有的屬於該表資料庫塊，即使該表中沒有任何資料。
b) 即使HWM以下有閒置資料庫塊，鍵入在插入資料時使用了append關鍵字，則在插入時使用HWM以上的資料區塊，此時HWM會自動增大。

如何知道一個表的HWM？
a) 首先對錶進行分析:
ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS;
b) SELECT blocks, empty_blocks, num_rows
FROM user_tables
WHERE table_name = <tablename>;

BLOCKS 列代表該表中曾經使用過得資料庫塊的數目，即水線。
EMPTY_BLOCKS 代表分配給該表，但是在水線以上的資料庫塊，即從來沒有使用的資料區塊。

讓我們以一個有28672行的BIG_EMP1表為例進行說明：
1) SQL> SELECT segment_name,segment_type,blocks
FROM dba_segments
WHERE segment_name='BIG_EMP1';
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
----------------------------- ----------------- ---------- -------
BIG_EMP1 TABLE 1024 2
1 row selected.

2) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

3) SQL> SELECT table_name,num_rows,blocks,empty_blocks
FROM user_tables
WHERE table_name='BIG_EMP1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
BIG_EMP1 28672 700 323
1 row selected.

注意：
BLOCKS + EMPTY_BLOCKS (700+323=1023)比DBA_SEGMENTS.BLOCKS少個資料庫塊，這是因為有一個資料庫塊被保留用作segment header。DBA_SEGMENTS.BLOCKS 表示分配給這個表的所有的資料庫塊的數目。USER_TABLES.BLOCKS表示已經使用過的資料庫塊的數目。

4) SQL> SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
FROM big_emp1;
Used
----------
700
1 row selected.

5) SQL> DELETE from big_emp1;
28672 rows processed.

6) SQL> commit;
Statement processed.

7) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

8) SQL> SELECT table_name,num_rows,blocks,empty_blocks
FROM user_tables
WHERE table_name='BIG_EMP1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
BIG_EMP1 0 700 323
1 row selected.

9) SQL> SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
FROM big_emp1;
Used
----------
0 -- 這表名沒有任何資料庫塊容納資料，即表中無資料
1 row selected.

10) SQL> TRUNCATE TABLE big_emp1;
Statement processed.

11) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

12) SQL> SELECT table_name,num_rows,blocks,empty_blocks
2> FROM user_tables
3> WHERE table_name='BIG_EMP1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
BIG_EMP1 0 0 511
1 row selected.

13) SQL> SELECT segment_name,segment_type,blocks
FROM dba_segments
WHERE segment_name='BIG_EMP1';
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
----------------------------- ----------------- ---------- -------
BIG_EMP1 TABLE 512 1
1 row selected.

注意:
TRUNCATE命令回收了由delete命令產生的空閑空間，注意該表分配的空間由原先的1024塊降為512塊。
為了保留由delete命令產生的空閑空間，可以使用
TRUNCATE TABLE big_emp1 REUSE STORAGE
用此命令後，該表還會是原先的1024塊。行連結(Row chaining) 與行遷移(Row Migration)
當一行的資料過長而不能插入一個單個資料區塊中時，可能發生兩種事情：行連結(row chaining)或行遷移(row migration)。

行連結
當第一次插入行時，由於行太長而不能容納在一個資料區塊中時，就會發生行連結。在這種情況下，oracle會使用與該塊連結的一塊或多塊資料區塊來容納該行的資料。行串連經常在插入比較大的行時才會發生，如包含long, long row, lob等類型的資料。在這些情況下行連結是不可避免的。

行遷移
當修改不是行連結的行時，當修改後的行長度大於修改前的行長度，並且該資料區塊中的空閑空間已經比較小而不能完全容納該行的資料時，就會發生行遷移。在這種情況下，Oracle會將整行的資料移轉到一個新的資料區塊上，而將該行原先的空間只放一個指標，指向該行的新的位置，並且該行原先空間的剩餘空間不再被資料庫使用，這些剩餘的空間我們將其稱之為空白洞，這就是產生表片段的主要原因，表片段基本上也是不可避免的，但是我們可以將其降到一個我們可以接受的程度。注意，即使發生了行遷移，發生了行遷移的行的rowid 還是不會變化，這也是行遷移會引起資料庫I/O效能降低的原因。其實行遷移是行連結的一種特殊形式，但是它的起因與行為跟行連結有很大不同，所以一般把它從行連結中獨立出來，單獨進行處理。

行連結和行遷移引起資料庫效能下降的原因：
引起效能下降的原因主要是由於引起多餘的I/O造成的。當通過索引訪問已有行遷移現象的行時，資料庫必須掃描一個以上的資料區塊才能檢索到改行的資料。這主要有一下兩種表現形式：
1) 導致row migration 或row chaining INSERT 或 UPDATE語句的效能比較差，因為它們需要執行額外的處理
2) 利用索引查詢已經連結或遷移的行的select語句效能比較差，因為它們要執行額外的I/O

如何才能檢測到行遷移與行連結：
在表中被遷移或被連結的行可以通過帶list chained rows選項的analyze語句識別出來。這個命令收集每個被遷移或連結的行的資訊，並將這些資訊放到指定的輸出表中。為了建立這個輸出表，運行指令碼UTLCHAIN.SQL。

SQL> ANALYZE TABLE scott.emp LIST CHAINED ROWS;
SQL> SELECT * FROM chained_rows;

當然你也可以通過檢查v$sysstat視圖中的'table fetch continued row'來檢查被遷移或被連結的行。

SQL> SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ---------
table fetch continued row 308

儘管行遷移與行連結是兩個不同的事情，但是在oracle內部，它們被當作一回事。所以當你檢測行遷移與行連結時，你應該仔細的分析當前你正在處理的是行遷移還是行連結。

解決辦法
o 在大多數情況下，行連結是無法克服的，特別是在一個表包含象LONGS, LOBs 等這樣的列時。當在不同的表中有大量的連結行，並且哪些表的行的長度不是很長時，你可以通過用更大的block size重建資料庫的方法來解決它。

例如：當前你的資料庫的資料區塊的大小為4K，但是你的行的平均長度為6k，那麼你可以通過用8k大小的資料區塊來重建資料庫的辦法解決行連結現象。

o 行遷移主要是由於設定的PCTFREE參數過小，導致沒有給update操作留下足夠的空閑空間引起。為了避免行遷移，所有被修改的表應該設定合適的PCTFREE 值，以便在每個資料區塊內為資料修改保留足夠的空間。可以通過增加PCTFREE值的辦法來避免行遷移，但這種解決辦法是以犧牲更多的空間為代價的，這也就是我們通常所說的以空間換效率。 而且通過增加PCTFREE值的辦法只能緩解行遷移現象，而不能完全解決行遷移，所以較好的辦法是在設定了合適的PCTFREE值的後，在發現行遷移現象比較嚴重時，對錶的資料進行重組。
下面是對行遷移資料進行重組的步驟(這種方法也被成為CTAS):
-- Get the name of the table with migrated rows:
ACCEPT table_name PROMPT 'Enter the name of the table with migrated rows: '

-- Clean up from last execution
set echo off
DROP TABLE migrated_rows;
DROP TABLE chained_rows;

-- Create the CHAINED_ROWS table
@.../rdbms/admin/utlchain.sql
set echo on
spool fix_mig
-- List the chained and migrated rows
ANALYZE TABLE &table_name LIST CHAINED ROWS;

-- Copy the chained/migrated rows to another table
create table migrated_rows as
SELECT orig.*
FROM &table_name orig, chained_rows cr
WHERE orig.rowid = cr.head_rowid
AND cr.table_name = upper('&table_name');

-- Delete the chained/migrated rows from the original table
DELETE FROM &table_name WHERE rowid IN (SELECT head_rowid FROM chained_rows);

-- Copy the chained/migrated rows back into the original table
INSERT INTO &table_name SELECT * FROM migrated_rows;

spool off

當對一個表進行全表掃描時，我們實際上忽略行遷移中各個指向其它行的指標，因為我們知道，全表掃描會遍曆全表，最終會讀到發生行遷移的行的行資料，在此時才會處理這些行資料。因此，在全表掃描中，行遷移不會引發其它額外的工作。
當通過索引讀一個表的資料時，被遷移的行會引起額外的I/O操作。這是因為從所引中我們會讀到資料行的rowid，它告訴資料庫到指定檔案的指定資料區塊的指定slot上可以找到需要的資料，但是因為發生了行遷移，此處只存放一個指向資料的指標，而不是真正的資料，所以資料庫又需要根據該指標(類似rowid)到指定檔案的指定資料區塊的指定slot上去找真正的資料，重複上面的過程，知道找到真正的資料。我們可以看出，這會引入額外的I/O操作。發現又嚴重表片段的表的步驟：
表需要整理原因有2:
a) 有太多的migration rows
b) 表經過刪除資料後有大量的空塊, 而全表掃描時，仍需要讀這些空塊

發現需要reorganization的表，需要從表的實際使用的空間與表的hwm入手

首先分析表：
Alter table emp compute statistics.

然後可以查詢出有資料的資料區塊的個數:
For ORACLE 7:
SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)||
SUBSTR(rowid,1,8)) "Used"
FROM schema.table;

For ORACLE 8+:
SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
FROM schema.table;
or
SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) "Used"
FROM schema.table;

查詢出HWM以下的資料區塊的個數(可能由於delete, 資料區塊中並不包含資料):
This will update the table statistics. After generating the statistics, to determine the high water mark:
SELECT blocks, empty_blocks, num_rows
FROM user_tables
WHERE table_name = <tablename>;

下面給出一個綜合的sql語句，它可以查詢出浪費空間的表(浪費超過25%)，而且還計算出其它資訊(使用時根據具體情況修改where子句中的blocks，owner限制條件)：
SELECT OWNER, SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE,
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,
ROUND(BYTES/1024, 2) TABLE_KB, NUM_ROWS,
BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MARK, AVG_USED_BLOCKS,
CHAIN_PER, EXTENTS, MAX_EXTENTS, ALLO_EXTENT_PER,
DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0,'N','Y') CAN_EXTEND_SPACE,
NEXT_EXTENT, MAX_FREE_SPACE,
O_TABLESPACE_NAME TABLESPACE_NAME
FROM
(SELECT A.OWNER OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.BYTES,
B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0),
0, 1,
ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0)
) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A,
SYS.DBA_TABLES B,
SYS.TS$ C
WHERE A.OWNER =B.OWNER and
SEGMENT_NAME = TABLE_NAME and
SEGMENT_TYPE = 'TABLE' AND
B.TABLESPACE_NAME = C.NAME
UNION ALL
SELECT A.OWNER OWNER, SEGMENT_NAME || '.' || B.PARTITION_NAME, SEGMENT_TYPE, BYTES,
B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0),
0, 1,
ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0)
) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A,
SYS.DBA_TAB_PARTITIONS B,
SYS.TS$ C,
SYS.DBA_TABLES D
WHERE A.OWNER = B.TABLE_OWNER and
SEGMENT_NAME = B.TABLE_NAME and
SEGMENT_TYPE = 'TABLE PARTITION' AND
B.TABLESPACE_NAME = C.NAME AND
D.OWNER = B.TABLE_OWNER AND
D.TABLE_NAME = B.TABLE_NAME AND
A.PARTITION_NAME = B.PARTITION_NAME),
(SELECT TABLESPACE_NAME F_TABLESPACE_NAME,MAX(BYTES)
MAX_FREE_SPACE
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME)
WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME AND
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2), 0) > 25
AND OWNER = '??' AND BLOCKS > 128
ORDER BY 10 DESC, 1 ASC, 2 ASC;

各列的說明：
WASTE_PER：已指派空間中水線以下的空閑空間(即浪費空間)的百分比。
TABLE_KB：該表目前已經分配的所有空間的大小，以k為單位。
NUM_ROWS：在在表中資料的行數
BLOCKS：該表目前已經分配的資料區塊的塊數，包含水線以上的部分
EMPTY_BLOCKS：已指派空間中水線以上的空閑空間
HIGHWATER_MARK：目前的水線
AVG_USED_BLOCKS：理想情況下(沒有行遷移)，該表資料應該佔用的資料區塊的個數
CHAIN_PER：發生行遷移現象的行佔總行的比率
EXTENTS：該表目前已經分配的extent數
MAX_EXTENTS：該表可以分配的最大extent的個數
ALLO_EXTENT_PER：目前已指派的extent的個數占可以分配最大extent的比率
CAN_EXTEND_SPACE：是否可以分配下一個extent
NEXT_EXTENT：下一個extent的大小
MAX_FREE_SPACE：表的已指派空間中最大的空閑空間