Oracle 統計資訊過舊導致效能劣化：實戰案例分析與解決方案

案例背景

某銀行核心系統，穩定運行多年。某批次作業原本 30 分鐘完成，突然暴增至 3+ 小時仍未結束。排除資料量暴增後，鎖定問題核心：執行計畫變更，根因為統計資訊過舊導致優化器誤判。

診斷流程與關鍵 SQL

Step 1：鎖定問題 Session 與 SQL_ID

-- 查詢長時間運行 Session
SELECT sid, serial#, sql_id, username, status, last_call_et
FROM v$session
WHERE status = 'ACTIVE' AND last_call_et > 1800  -- 超過 30 分鐘
  AND username IS NOT NULL;

Step 2：分析歷史執行統計

-- 透過 AWR 歷史檢視 SQL 執行趨勢
SELECT b.begin_interval_time,
       a.plan_hash_value,
       a.executions_total,
       a.elapsed_time_total/1e6 elapsed_sec,
       a.cpu_time_total/1e6 cpu_sec,
       a.buffer_gets_total,
       a.disk_reads_total,
       a.rows_processed_total
FROM dba_hist_sqlstat a
JOIN dba_hist_snapshot b ON a.snap_id = b.snap_id
WHERE a.sql_id = '&sql_id'
ORDER BY b.begin_interval_time DESC;

Step 3：對比執行計畫變更

-- 檢查相關表統計資訊時間
SELECT table_name, last_analyzed, num_rows, blocks, sample_size
FROM dba_tables
WHERE owner = '&owner' AND table_name IN ('TARGET_TABLE_1', 'TARGET_TABLE_2');

-- 檢查索引統計資訊
SELECT index_name, last_analyzed, distinct_keys, clustering_factor
FROM dba_indexes
WHERE table_owner = '&owner' AND table_name = 'TARGET_TABLE';

根因分析

• 統計資訊停留在數周前，實際資料分布已大幅變化（如：原本均勻分布變成高度傾斜、資料量增長 10 倍）。
• 優化器依據舊統計資訊選擇了全表掃描而非索引範圍掃描，或錯誤選擇了 Hash Join 而非 Nested Loop。
• Plan Hash Value 變更：AWR 顯示同一 SQL_ID 對應多個 Plan Hash Value，且新計畫 Cost 高、執行時間長。

解決方案：手動收集統計資訊

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname          => 'SCOTT',           -- Schema 名稱
  tabname          => 'TARGET_TABLE',    -- 表名稱
  estimate_percent => 10,                -- 抽樣百分比（100 為全表，預設 DBMS_STATS.AUTO_SAMPLE_SIZE）
  method_opt       => 'FOR ALL COLUMNS SIZE AUTO',  -- 直方圖自動收集
  degree           => 8,                 -- 平行度
  granularity      => 'GLOBAL',          -- 全域/分區統計
  cascade          => TRUE,              -- 同步收集索引統計
  no_invalidate    => FALSE              -- TRUE=不立即失效游標，FALSE=立即重解析
);

參數調整建議

預防機制：自動統計資訊收集

-- 1. 從游標快取載入基線
DECLARE
  v_cnt PLS_INTEGER;
BEGIN
  v_cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '&sql_id',
    plan_hash_value => &plan_hash_value,
    fixed => 'YES'  -- 固定不變更
  );
  DBMS_OUTPUT.PUT_LINE('Loaded ' || v_cnt || ' plans');
END;

-- 2. 查詢基線狀態
SELECT sql_handle, plan_name, enabled, accepted, fixed, reproduced
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%TARGET_TABLE%';

驗證成效

• 統計資訊收集完成後，問題作業從 3+ 小時恢復至 25 分鐘（優於原本 30 分鐘，因統計資訊更精準）。
• 後續建立每日統計資訊收集監控報表，確保關鍵表統計資訊鮮度 < 24 小時。
• 引入 SPM 固定核心報表 SQL 計畫，消除計畫波動風險。

總結

統計資訊是 CBO 優化器的「眼睛」。過舊統計資訊導致計畫劣化是資料庫效能退化的首要原因。建立「自動收集+手動補充+關鍵表監控+SPM 固定」四層防護體系，是維持穩定效能的關鍵。