Oracle 數據統計與優化器統計信息收集指南

Oracle 優化器模式概述

Oracle 提供兩種優化器：基於成本的優化器（CBO）與基於規則的優化器（RBO）。優化器選擇由初始化參數 OPTIMIZER_MODE 決定，可選值包括 CHOOSE、RULE、ALL_ROWS、FIRST_ROWS。Oracle 9i 後新增 FIRST_ROWS_1、FIRST_ROWS_10、FIRST_ROWS_100、FIRST_ROWS_1000 等精細控制參數。官方強烈建議使用 CBO，若採用 CBO，必須定期收集數據統計信息，確保統計數據的時效性，否則執行計劃可能偏離最優路徑。

統計信息收集方法

1. ANALYZE 命令（傳統方式）

• 完全統計：ANALYZE TABLE table_name COMPUTE STATISTICS;
• 抽樣估計：ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 30 PERCENT;

2. 定時腳本自動收集（Unix/Linux 環境）

以 Oracle 用戶身份編寫 Shell 腳本，動態生成 SQL 再執行。以下示例針對 SCOTT 用戶下所有表與索引進行 20% 抽樣分析：

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，按月或適當頻率執行。亦可保存為 analyze.sql 在 SQL*Plus 直接運行。

3. 內建套件程序（推薦）

DBMS_DDL.ANALYZE_OBJECT

針對單一對象（表、索引、簇表）分析：

DBMS_DDL.ANALYZE_OBJECT(
  TYPE VARCHAR2,        -- TABLE, INDEX, CLUSTER
  SCHEMA VARCHAR2,      -- 對象擁有者
  NAME VARCHAR2,        -- 對象名稱
  METHOD VARCHAR2,      -- ESTIMATE, COMPUTE, DELETE
  ESTIMATE_ROWS NUMBER DEFAULT NULL,
  ESTIMATE_PERCENT NUMBER DEFAULT NULL,
  METHOD_OPT VARCHAR2 DEFAULT NULL,
  PARTNAME VARCHAR2 DEFAULT NULL);

示例：DBMS_DDL.ANALYZE_OBJECT('TABLE', 'SCOTT', 'EMP', 'ESTIMATE', NULL, 50); 表示對 EMP 表進行 50% 抽樣。

DBMS_UTILITY.ANALYZE_SCHEMA / ANALYZE_DATABASE

分別對指定 Schema 或整個數據庫下所有 TABLE、INDEX、CLUSTER 進行統計：

DBMS_UTILITY.ANALYZE_SCHEMA(
  SCHEMA VARCHAR2,
  METHOD VARCHAR2,
  ESTIMATE_ROWS NUMBER DEFAULT NULL,
  ESTIMATE_PERCENT NUMBER DEFAULT NULL,
  METHOD_OPT VARCHAR2 DEFAULT NULL);

DBMS_UTILITY.ANALYZE_DATABASE(
  METHOD VARCHAR2,
  ESTIMATE_ROWS NUMBER DEFAULT NULL,
  ESTIMATE_PERCENT NUMBER DEFAULT NULL,
  METHOD_OPT VARCHAR2 DEFAULT NULL);

DBMS_STATS 套件（Oracle 8i 引入，功能最強）

支援並行收集、更靈活的參數控制：

• DBMS_STATS.GATHER_TABLE_STATS
• DBMS_STATS.GATHER_INDEX_STATS
• DBMS_STATS.GATHER_SCHEMA_STATS
• DBMS_STATS.GATHER_DATABASE_STATS

4. 利用 DBMS_JOB 定時任務（數據庫內部調度）

以下示例每 30 天對 SCOTT Schema 執行一次 20% 抽樣分析：

VARIABLE jobno number;
BEGIN
  DBMS_JOB.SUBMIT(
    :jobno,
    'dbms_utility.analyze_schema(''scott'', ''estimate'', NULL, 20);',
    SYSDATE,
    'SYSDATE+30');
  COMMIT;
END;
/
PRINT jobno

輸出 JOBNO = 16 表示提交成功，系統將自動按排程執行。