Oracle DELETE 後表空間未釋放與效能暴跌：ANALYZE/DBMS_STATS 統計資訊同步實戰解析

現象描述

大量 DELETE 後，表大小 (Segment) 未縮減、High Water Mark (HWM) 不動、查詢效能不升反降 (甚至從秒級變小時級)。根因：DELETE 僅標記資料列刪除、不釋放區塊、不降低 HWM、不自動更新統計資訊，導致優化器誤判基數、選錯執行計劃 (如改走 Full Table Scan)。

解決方案對比

方案一：ANALYZE TABLE COMPUTE STATISTICS (9i/10g 常用)

ANALYZE TABLE itemlog COMPUTE STATISTICS;
-- 或針對索引
ANALYZE TABLE ysgl_compile_reqsub COMPUTE STATISTICS FOR ALL INDEXES;

效果：統計資訊同步後，優化器重新評估，執行計劃恢復正常 (如回到 Index Range Scan)。

方案二：EXP/IMP 重建表 (傳統暴力法)

EXP ... TABLES=itemlog FILE=itemlog.dmp
DROP TABLE itemlog;
IMP ... TABLES=itemlog FILE=itemlog.dmp

效果：重建表隱含重建索引、重置 HWM、重新收集統計資訊。缺點：需停機、耗時長、不適用大表。

方案三：ALTER TABLE ... MOVE + INDEX REBUILD (10g+ 推薦)

ALTER TABLE itemlog MOVE TABLESPACE users;  -- 重組表、降低 HWM
-- 需同步 REBUILD 所有索引
ALTER INDEX idx_itemlog_1 REBUILD;
ALTER INDEX idx_itemlog_2 REBUILD;
-- 最後收集統計資訊
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ITEMLOG', CASCADE=>TRUE);

方案四：DBMS_REDEFINITION 線上重組 (11g+ 零停機)

-- 建立中間表、同步資料、切換，全程線上，適用 7x24 系統

核心觀念：統計資訊與執行計劃的關係

實務建議：統計資訊收集最佳實務

1. 全面改用 DBMS_STATS：ANALYZE 已棄用，DBMS_STATS 支援並行、增量、歷史、發佈/暫存機制。
2. 固定排程收集：DBMS_STATS.GATHER_SCHEMA_STATS 或 GATHER_DATABASE_STATS，維護窗口自動執行 (10g+ AUTO_TASK 預設啟用)。
3. 大表採增量統計：DBMS_STATS.SET_TABLE_PREFS('SCHEMA','BIG_TABLE','INCREMENTAL','TRUE') + 分區表，僅掃描變更分區。
4. 關鍵表鎖定統計：DBMS_STATS.LOCK_TABLE_STATS 防止自動收集覆蓋人工調優後的統計。
5. TRUNCATE/大量 DML 後立即同步：ETL 流程最後一步加入 GATHER_TABLE_STATS。

常見誤區

• 以為 DELETE 會釋放空間：需 MOVE 或 SHRINK SPACE (啟用 Row Movement) 才能降低 HWM。
• 以為 TRUNCATE 會重置統計資訊：TRUNCATE 重置 HWM、釋放 Extents，但 不更新統計資訊，需手動收集。
• 盲目收集導致計劃翻轉：重要系統建議用 DBMS_STATS.SET_*_PREFS 設定 PUBLISH=FALSE 先暫存，驗證無誤再發佈。