Oracle 數據庫整理表碎片

表碎片的來源

當針對一個表的刪除操作很多時，表會產生大量碎片。刪除操作釋放的空間不會被插入操作立即重用，甚至永遠也不會被重用。

怎樣確定是否有表碎片

-- 收集表統計信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCHEMA_NAME',tabname=> 'TABLE_NAME');

-- 確定碎片程度

SQL> 或者使用如下gist中的腳本找出某個 Schema 中表碎片超過25%的表。使用此腳本前，先確定 Schema 中表統計信息收集完整。

SELECT table_name,

ROUND((blocks * 8), 2) "高水位空間 k",

ROUND((num_rows * avg_row_len / 1024), 2) "真實使用空間 k",

ROUND((blocks * 10 / 100) * 8, 2) "預留空間(pctfree) k",

ROUND((blocks * 8 - (num_rows * avg_row_len / 1024) -

blocks * 8 * 10 / 100),

2) "浪費空間 k"

FROM dba_tables

WHERE table_name = 'BP_RESERVE_ORDERLIST';

-- 查看表上次收集統計信息時間

select table_name,last_analyzed from dba_tables where owner = 'SCHEMA_NAME'

-- 收集整個 Schema 中對象的統計信息

SQL> exec dbms_stats.gather_schema_stats(ownname=>'SCHEMA_NAME');

為什么要整理表碎片

Oracle 對數據段的管理有一個高水位(HWM, High Water Mark)的概念。高水位是數據段中使用過和未使用過的數據塊的分界線。高水位以下的數據塊是曾使用過的，以上的是從未被使用或初始化過的。

當 Oracle 進行全表掃描(FTS, Full table scan)的操作時，它會讀高水位下的所有數據塊。如果高水位下還有很多空閑空間（碎片），讀取這些空閑數據塊會降低操作的性能。

行鏈接和行遷移

• 行鏈接 Row Chaining：當插入數據量大的行的，如果一個Block不能存放一條記錄，該記錄的一部分會存儲到同個Extent中的其他Block，這些block形成一個數據塊鏈。
• 行遷移 Row Migration：當Update的時候導致記錄長度增加了，存儲的Block已經滿了，就會發生行遷移。Oracle會遷移整行數據到一個能夠存儲下整行數據的Block中，遷移的原始指針指向新的存放行數據的Block，ROWID不變。

當數據行發生鏈接（chain）或遷移（migrate）時，對其訪問將會造成 I/O 性能降低，因為Oracle為獲取這些數據行的數據，必須訪問更多的數據塊（data block）。

表碎片導致的問題

• 查詢響應時間(尤其是全表掃描)變慢
• 產生大量行遷移
• 浪費空間

整理表碎片對基于索引的查詢不會有太大性能提升。

如何整理表碎片

10g之前

兩種方法：

• 導出表，刪除表，再導入表
• alter table move

一般選擇第二種，需要重建索引。

10g后

從 10g 開始，提供一個 shrink 命令，需要表空間是基于自動段管理的。

可以分成兩步操作：

-- 整理表，不影響DML操作

SQL> alter table TABLE_NAME shrink space compact;

-- 重置高水位，此時不能有DML操作

SQL> alter table TABLE_NAME shrink space;

也可以一步到位：

-- 整理表，并重置高水位

SQL> alter table TABLE_NAME shrink space;

shrink 的優勢：

• 不需要重建索引。
• 可以在線操作。