表碎片的相關知識（ZT）

表碎片的相關知識

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

表碎片的相關知識

什麼是水線(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  ESTIMATE/COMPUTE STATISTICS;
b) SELE blocks, empty_blocks, num_rows 
FROM user_tables 
WHERE table_name = ;

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. 

注意：

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內部，它們被當作一回事。所以當你檢測行遷移與行連結時，你應該仔細的分析當前你正在處理的是行遷移還是行連結。

解決辦法

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

-- 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

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;

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 = ;

下面給出一個綜合的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 = 'ARBVW'
-- 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：表的已分配中最大的空閒空間。