您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 統計資訊收集全策略：CBO 依賴、DBMS_STATS、排程自動化與實務誤區

編輯：Oracle 資料庫基礎

Oracle 成本基礎優化器（CBO）的決策品質，直接取決於統計資訊的準確度與時效性。本文系統說明統計資訊的兩大收集模式（完全計算 vs 抽樣估算）、三種自動化實作路徑（手工腳本、DBMS_UTILITY、DBMS_STATS）、排程策略，並釐清常見誤區，助你建立穩健的統計資訊治理機制。

一、為何統計資訊決定 CBO 生死

RBO（規則基礎）已不再增強；所有新特性（星狀連接、Hash Join、分區表、平行查詢等）僅支援 CBO。
CBO 計算各執行計畫的 Cost，Cost 依據為：表/索引/欄位的統計分布（行數、區塊數、相異值數、直方圖等）。
統計過期 → Cost 偏差 → 執行計畫劣化 → 效能暴跌。

二、收集模式：完全計算 vs 抽樣估算

模式	語法範例	耗時	精度	適用場景
完全計算	`ANALYZE TABLE abc COMPUTE STATISTICS;`	等同全表掃描	100%	小表、索引、需精確直方圖
抽樣估算	`ANALYZE TABLE abc ESTIMATE STATISTICS SAMPLE 20 PERCENT;`	大幅縮短	統計誤差可控	大表、例行維護、非關鍵欄位

建議策略：表用抽樣（10%~30%），索引用完全計算；關鍵歪斜欄位額外建立直方圖（METHOD_OPT => 'FOR COLUMNS SIZE 254 col_name'）。

三、三種自動化實作路徑

1. 傳統 Shell + SQL*Plus 腳本（舊版相容）

# analyze.sh
sqlplus scott/tiger << EOF
SET PAGESIZE 5000
SET HEADING OFF
SPOOL analtab.sql
SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 20 PERCENT;' FROM USER_TABLES;
SPOOL OFF
SPOOL analind.sql
SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES;' FROM USER_TABLES;
SPOOL OFF
SPOOL analyze.log
@analtab.sql
@analind.sql
SPOOL OFF
EXIT
EOF

加入 crontab 定時執行（如每月 1 號 02:00）。

2. DBMS_UTILITY 套件（8i/9i 常用）

-- 單一物件
EXEC DBMS_DDL.ANALYZE_OBJECT('TABLE','SCOTT','EMP','ESTIMATE',NULL,50);

-- 整個 Schema
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE',NULL,20);

-- 整個資料庫
EXEC DBMS_UTILITY.ANALYZE_DATABASE('ESTIMATE',NULL,20);

缺點：無法平行、控制粒度粗、不支援直方圖細節。

3. DBMS_STATS（9i+ 推薦，功能最完整）

-- 表級（含索引、欄位、直方圖）
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT', tabname => 'EMP',
    estimate_percent => 20, method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE, degree => 4);  -- degree 啟用平行

-- Schema 級
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname => 'SCOTT', estimate_percent => 20,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE, degree => 4);

-- 資料庫級
EXEC DBMS_STATS.GATHER_DATABASE_STATS(
    estimate_percent => 10, method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE, degree => 8);

關鍵參數：

ESTIMATE_PERCENT：DBMS_STATS.AUTO_SAMPLE_SIZE（11g+ 自動決定最佳取樣率）。
METHOD_OPT：FOR ALL COLUMNS SIZE AUTO 讓 Oracle 自動判斷哪些欄位需直方圖。
CASCADE => TRUE：同步收集索引統計。
DEGREE：平行度，配合 PARALLEL_DEGREE_POLICY 使用。
NO_INVALIDATE => FALSE：收集後立即失效相關 Cursor（預設 TRUE，可能延遲生效）。

四、排程自動化最佳實務

11g+ 自動統計收集作業：預設啟用 AUTO_STATS_ADVISOR 與 GATHER_STATS_JOB（維護視窗內自動收集），檢視 DBA_AUTOTASK_CLIENT 確認狀態。

自訂 DBMS_SCHEDULER 作業（針對特殊需求）：

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'MY_GATHER_STATS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''SCOTT'', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>''FOR ALL COLUMNS SIZE AUTO'', cascade=>TRUE); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
    enabled         => TRUE,
    comments        => 'Daily stats gather for SCOTT schema');
END;
/

分區表漸進式統計（11g+）：INCREMENTAL => TRUE + GRANULARITY => 'PARTITION'，僅收集變更分區，再由全域統計聚合，大幅降低大表維護成本。
鎖定統計：對極少變動的查詢表（如代碼表），用 DBMS_STATS.LOCK_TABLE_STATS 防止意外覆寫。
匯出/匯入統計：跨環境同步或備份統計，用 EXPORT_SCHEMA_STATS/IMPORT_SCHEMA_STATS。

五、實務常見誤區與避坑指南

誤區	後果	正確做法
只收集表不收集索引	CBO 低估索引存取成本，導致全表掃描	`CASCADE => TRUE` 或單獨 `GATHER_INDEX_STATS`
長期不收集，統計過期	執行計畫劣化，效能逐漸下降	建立定期排程 + 變更後即時收集
大表用 100% 完全計算	維護窗口超時、I/O 暴增	抽樣 + 增量/分區統計
忽略歪斜欄位直方圖	Bind Variable Peeking 導致計畫不穩	`METHOD_OPT => 'FOR COLUMNS SIZE 254 col_name'`
收集後未驗證執行計畫	壞統計導致更差計畫卻不知情	收集前後對比 `EXPLAIN PLAN` / `SQL MONITOR`

六、驗證統計資訊品質

-- 檢視表/索引/欄位統計最後收集時間
SELECT table_name, last_analyzed, num_rows, blocks, sample_size
FROM   user_tables
ORDER BY last_analyzed;

-- 檢視直方圖
SELECT column_name, histogram, num_buckets
FROM   user_tab_col_statistics
WHERE  table_name = 'EMP' AND histogram != 'NONE';

-- 找出久未收集的物件（超過 30 天）
SELECT owner, table_name, last_analyzed
FROM   dba_tables
WHERE  last_analyzed < SYSDATE - 30
ORDER BY last_analyzed;

統計資訊治理不是「設定好就不用管」，而是持續的「收集 → 驗證 → 調整 → 鎖定」循環。將 DBMS_STATS 納入 CI/CD 流程（Schema 變更後自動收集）、建立統計品質儀表板，才能讓 CBO 持續產出最優計畫，守護資料庫效能底線。