Oracle 區塊效能優化：高水位線、遷移鏈結與索引重組實戰

避免動態擴展缺陷

採用本地管理表空間（LMT），合理設定區段初始大小與擴展策略，監控即將擴展的區段：

SELECT owner, table_name, blocks, empty_blocks
FROM dba_tables
WHERE empty_blocks / (blocks + empty_blocks) < 0.1;

高水位線 (HWM) 管理

HWM 記錄於區段標頭區塊，隨資料插入以 5 區塊為單位增長。TRUNCATE 可重置 HWM，DELETE 則不行。全表掃描會讀取 HWM 以下所有區塊，HWM 以上空間雖不影響性能但浪費儲存。

回收 HWM 以上空間： ALTER TABLE ... DEALLOCATE UNUSED;

降低 HWM（整理碎片）： 匯出/匯入、ALTER TABLE ... MOVE（需重建索引）。

表統計資訊收集與分析

ANALYZE TABLE schema.table_name COMPUTE STATISTICS;
SELECT num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
FROM dba_tables WHERE owner='...' AND table_name='...';

區塊大小選擇權衡

• 小區塊 (2K-8K)：適合 OLTP，減少爭用，有利小列隨機存取，但索引層級較深。
• 大區塊 (16K-64K)：適合 DSS/資料倉儲，減少 I/O 次數，但可能增加爭用。
• 區塊大小應為作業系統區塊大小的整數倍，建庫時設定後難以變更。

PCTFREE 與 PCTUSED 設定指南

列遷移與鏈結：偵測與消除

現象區別

• 鏈結：單列過大，跨多區塊儲存，INSERT/UPDATE 均可能導致，部分情況無法避免。
• 遷移：UPDATE 導致列增大，原區塊放不下，整列移至新區塊，原位留下指標（ROWID 不變）。

兩者皆降低 DML 性能、增加索引查詢 I/O。

偵測腳本

ANALYZE TABLE table_name LIST CHAINED ROWS;
SELECT owner_name, table_name, head_rowid FROM chained_rows WHERE table_name='...';

消除遷移列步驟

1. 建立 CHAINED_ROWS 表（@?/rdbms/admin/utlchain）。
2. ANALYZE TABLE ... LIST CHAINED ROWS;
3. 複製遷移列至暫存表，從原表刪除，再插回原表。
4. 執行前必須移除相關外鍵約束。

索引重組策略

判斷是否需重建

ANALYZE INDEX idx_name VALIDATE STRUCTURE;
SELECT name, (del_lf_rows_len / lf_rows_len) * 100 AS waste_pct FROM index_stats;

浪費空間超過 20% 時建議 ALTER INDEX ... REBUILD；或用 COALESCE 整理葉區塊。

監控未使用索引

ALTER INDEX idx_name MONITORING USAGE;
-- 一段時間後 --
ALTER INDEX idx_name NOMONITORING USAGE;
SELECT index_name, used FROM v$object_usage;

刪除長期未使用的索引可降低 DML 開銷。