Oracle SQL 執行緩慢根因分析與索引維護最佳實務

問題情境

某系統擁有一張超過 2 億筆資料的大表，並建立 4 個獨立索引。業務需求每日兩次各插入 300 萬筆資料，單次耗時超過 3 小時，嚴重影響效能。為改善狀況，團隊調整策略：每日 TRUNCATE 該表後僅保留當日新增資料（約 300 萬筆）。

詭異現象：資料量減少、執行時間卻暴增

咨詢 DBA 後建議重建索引，重建後耗時縮減至 6 秒。然而第三天問題再次發生，DBA 亦無法給出合理解釋，推測與 Oracle 複雜的查詢優化器演算法有關。

最終解決方案：標準化維護流程

DBA 給出的標準化作業流程如下：

TRUNCATE TABLE ...;
DROP INDEX ...;
INSERT DATA ...;
CREATE INDEX ...;
ANALYZE TABLE table_name COMPUTE STATISTICS;

執行上述流程後，整體操作耗時大幅降低並穩定。

核心觀念釐清

為何 TRUNCATE 後必須重建索引與統計資訊？

• TRUNCATE 會釋放 High Water Mark (HWM)，但索引結構仍保留原有統計資訊，導致優化器誤判資料分布。
• 統計資訊過期：資料量從 2 億驟降至 300 萬，舊統計資訊嚴重偏離實際，導致執行計劃錯誤（如改走全表掃描）。
• 索引碎片：大量刪除/插入操作會產生索引碎片，影響範圍掃描效能。

最佳實務建議

1. 固化維護腳本：將 TRUNCATE → DROP INDEX → LOAD → CREATE INDEX → GATHER STATS 納入排程作業，避免人為疏漏。
2. 使用 DBMS_STATS 取代 ANALYZE：Oracle 10g 起建議改用 DBMS_STATS.GATHER_TABLE_STATS，功能更完整、支援並行與增量統計。
3. 考慮 Partition 策略：若資料具時效性，採用分區表配合 TRUNCATE PARTITION 可避免全域索引失效問題。
4. 監控執行計劃變化：善用 SQL Plan Baseline 或 Adaptive Cursor Sharing 穩定關鍵 SQL 計劃。