Oracle SQL執行緩慢根因分析：從優化器模式到統計資訊失效的實戰案例

三種優化器模式與選擇陷阱

Oracle提供三種優化器模式：

• RULE（基於規則）：依固定規則生成執行計劃，不考慮資料分佈。
• COST / CBO（基於成本）：根據統計資訊估算成本，選擇最低成本路徑。
• CHOOSE（選擇性）：若表有統計資訊則用CBO，否則退回RULE。

預設為CHOOSE，但這是隱患：統計資訊過期或缺失會導致優化器誤判，產生全表掃描等劣質計劃。生產環境建議顯式設定為ALL_ROWS或FIRST_ROWS，並定時收集統計資訊。

實戰案例：Truncate後Update效能暴跌之謎

現象

• 表原有2億行，4個獨立索引，每日兩次各插入300萬行，耗時3小時+。
• 改為「每日Truncate後只存當天新增數據（約300萬行）」。
• 次日對此表Update極慢：2億行時59秒，300萬行卻耗時數小時。
• 重建索引後恢復6秒，但第三天問題復發。

根因分析

1. Truncate不重置索引統計資訊：Truncate保留索引結構，但高水位線歸零，統計資訊（如NUM_ROWS、LEAF_BLOCKS、CLUSTERING_FACTOR）未同步更新，仍反映舊資料量。
2. 優化器被舊統計誤導：CBO以為表仍有2億行，選擇全表掃描而非索引存取，導致效能崩潰。
3. 重建索引副作用收集統計：ALTER INDEX REBUILD隱含收集索引統計，暫時修正計劃；但隨DML進行，統計再次偏離。

標準解決流程（DBA最佳實踐）

TRUNCATE TABLE target_table;
DROP INDEX idx_1; DROP INDEX idx_2; ...  -- 刪除所有索引
-- 執行大量載入
INSERT /*+ APPEND */ INTO target_table SELECT ...;
-- 重建索引
CREATE INDEX idx_1 ON ...; CREATE INDEX idx_2 ON ...;
-- 關鍵：收集表與索引統計資訊
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCHEMA', tabname=>'TARGET_TABLE', cascade=>TRUE);

或使用ANALYZE TABLE ... COMPUTE STATISTICS（10g前），11g+強烈建議DBMS_STATS。

核心啟示

大量資料變更（Truncate/Partition Exchange/大量載入）後，必須同步更新統計資訊。依賴自動收集（Auto Task）有時間窗延遲，關鍵作業應顯式調用DBMS_STATS，並設定NO_INVALIDATE=>FALSE立即刷新游標。