Oracle資料操作與控制語言：統計資訊管理實戰詳解

優化器架構與統計資訊關鍵性

Oracle 提供 RBO（規則基礎）與 CBO（成本基礎）兩種優化器。CBO 自 7 版引入，支援雜湊連結、分區表、平行查詢等現代特性，透過統計資訊計算執行計劃成本選擇最優路徑。統計資訊時效性直接決定 CBO 效能表現。

統計資訊生成兩大模式

完全計算法

ANALYZE TABLE abc COMPUTE STATISTICS;

等同全表掃描，耗時較長，適用小表或需精確統計場景。

抽樣估算法

ANALYZE TABLE abc ESTIMATE STATISTICS SAMPLE 20 PERCENT;

速度較快，建議大型表採用 10%-30% 抽樣；索引建議完全計算。

三種自動化收集方案對比

方案一：Shell + SQL*Plus 排程腳本

UNIX 環境下生成動態 SQL 腳本再執行，適合傳統環境：

sqlplus scott/tiger << EOF
SET PAGESIZE 5000
SET HEADING OFF
SPOOL ANALYTAB.SQL
SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 20 PERCENT;' FROM USER_TABLES;
SPOOL OFF
SPOOL ANALYIND.SQL
SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES;' FROM USER_TABLES;
SPOOL OFF
SPOOL ANALYZE.LOG
@ANALYTAB.SQL
@ANALYIND.SQL
SPOOL OFF
EXIT
EOF

加入 crontab 定期執行（如每月一次）。

方案二：DBMS 內建套件（8i 前主流）

DBMS_DDL.ANALYZE_OBJECT

單一物件精準控制：

DBMS_DDL.ANALYZE_OBJECT(
  TYPE VARCHAR2,        -- TABLE/INDEX/CLUSTER
  SCHEMA VARCHAR2,      -- 擁有者，NULL為當前用戶
  NAME VARCHAR2,        -- 物件名稱
  METHOD VARCHAR2,      -- ESTIMATE/COMPUTE/DELETE
  ESTIMATE_ROWS NUMBER DEFAULT NULL,
  ESTIMATE_PERCENT NUMBER DEFAULT NULL,
  METHOD_OPT VARCHAR2 DEFAULT NULL,  -- FOR TABLE / FOR ALL INDEXED COLUMNS / FOR ALL INDEXES
  PARTNAME VARCHAR2 DEFAULT NULL);   -- 分區名稱

-- 範例：SCOTT.EMP 表 50% 抽樣
DBMS_DDL.ANALYZE_OBJECT('TABLE', 'SCOTT', 'EMP', 'ESTIMATE', NULL, 50);

DBMS_UTILITY 批量分析

• ANALYZE_SCHEMA(schema, method, ...)：指定使用者所有物件
• ANALYZE_DATABASE(method, ...)：整個資料庫

方案三：DBMS_STATS（8i+ 推薦標準）

功能最完整，支援平行處理、增量統計、統計歷史保留：

• GATHER_TABLE_STATS
• GATHER_INDEX_STATS
• GATHER_SCHEMA_STATS
• GATHER_DATABASE_STATS

資料庫 Job 排程實戰

VARIABLE jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:jobno,
    'dbms_utility.analyze_schema("scott","estimate",NULL,20);',
    SYSDATE, 'SYSDATE+30');
  COMMIT;
END;
/
-- Job 16 每 30 天執行一次 SCOTT 結構 20% 抽樣分析

最佳實踐總結

核心原則：統計資訊必須反映真實資料分布，大量 DML 操作後必須重新收集，否則 CBO 可能產生劣質執行計劃。