Oracle 統計資訊收集與優化器運作機制深度解析

Oracle 優化器分為規則基礎（RBO）與成本基礎（CBO）兩種。CBO 依賴統計資訊計算執行計畫成本，選擇最低成本路徑。本文詳細說明統計資訊的收集方式、DBMS 套件應用、排程自動化策略，助您確保 CBO 高效運作。

一、優化器演進與統計資訊關鍵性

• RBO：依固定規則，不受資料量變化影響，Oracle 10g 後已棄用。
• CBO：自 7 版引入，支援星狀連接、哈希連接、分區表、平行查詢等進階功能。統計資訊的及時性與準確度直接決定 CBO 好壞。

二、統計資訊收集兩大法

三、三種自動化收集策略

1. Shell + SQL*Plus 排程（傳統做法）

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 定期執行。

2. DBMS_UTILITY / DBMS_DDL 套件

• DBMS_DDL.ANALYZE_OBJECT：單一物件（表/索引/叢集）
• DBMS_UTILITY.ANALYZE_SCHEMA：某 Schema 所有物件
• DBMS_UTILITY.ANALYZE_DATABASE：全資料庫

參數包含 METHOD（ESTIMATE/COMPUTE/DELETE）、ESTIMATE_PERCENT、METHOD_OPT 等。

3. DBMS_STATS 套件（8i+ 推薦，支援平行與更細緻控制）

DBMS_STATS.GATHER_TABLE_STATS
DBMS_STATS.GATHER_INDEX_STATS
DBMS_STATS.GATHER_SCHEMA_STATS
DBMS_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;
/
-- 每 30 天對 SCOTT 所有物件進行 20% 抽樣分析

五、最佳實務建議

1. 大表採抽樣（10~30%），小表可完全計算。
2. 索引建議完全計算，確保選擇率準確。
3. 資料大量異動後（載入、清除、分區交換）必須重新收集。
4. 優先使用 DBMS_STATS，並設定 DEGREE => DBMS_STATS.AUTO_DEGREE 啟用平行收集。