Oracle資料庫統計資訊生成機制與最佳實踐指南

Oracle優化器核心架構解析

Oracle資料庫的SQL執行效能核心在於優化器，主要分為基於規則的優化器（RBO）與基於成本的優化器（CBO）。RBO依賴固定語法規則，執行計劃不受資料變動影響；而CBO自Oracle 7版引入，成為現代Oracle核心優化引擎，支援星型連接、雜湊連結、反向索引、分區表與平行查詢等先進特性。CBO透過計算各執行計劃的「成本」，選擇成本最低方案執行，其計算依據來自資料表的統計分布資訊。

統計資訊的重要性與生成策略

CBO的效能表現高度依賴統計資訊的準確性與時效性。統計資訊生成主要有兩種方式：

完全計算法

ANALYZE TABLE abc COMPUTE STATISTICS;

耗時相當於全表掃描，適用於需精確統計的小型表或索引。

抽樣估算法

ANALYZE TABLE abc ESTIMATE STATISTICS SAMPLE 20 PERCENT;

採用抽樣方式，速度較快，建議用於大型表分析；索引分析可考慮完全計算法。

自動化統計資訊收集方案

方案一：排程腳本自動化

在UNIX平台建立分析腳本，透過crontab定期執行：

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

方案二：內建套件程式管理

Oracle提供多層級分析套件：

DBMS_DDL.ANALYZE_OBJECT

針對單一物件分析，支援TABLE、INDEX、CLUSTER類型：

DBMS_DDL.ANALYZE_OBJECT(
  TYPE VARCHAR2,
  SCHEMA VARCHAR2,
  NAME VARCHAR2,
  METHOD VARCHAR2,
  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);

DBMS_UTILITY 套件

• ANALYZE_SCHEMA：分析指定使用者所有物件
• ANALYZE_DATABASE：分析整個資料庫

DBMS_STATS 套件（Oracle 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;
/
-- 每30天自動執行 SCOTT 結構下所有物件的 20% 抽樣分析

最佳實踐建議

• 大型表採用抽樣估算（10%-30%），索引採用完全計算
• 建立定期自動收集機制（Job 或排程腳本）
• 資料大量異動後及時重新收集統計資訊
• 優先使用 DBMS_STATS 套件，功能更完善且支援平行處理