講解Oracle表碎片的具體起因及解決的辦法

2024-08-29 13:52:03

字體：大中小

來源：轉載

供稿：網友

表碎片的相關知識：

什么是水線(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) SELECT 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.

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;