Oracle 11g 統計信息自動收集配置與管理最佳實踐

背景

• 統計信息 = CBO 優化器生成執行計劃的基石
• 10g+ 預設 CBO 模式，資料庫建立即啟用自動收集作業

一、自動收集作業查看

SELECT client_name, status
FROM dba_autotask_client
WHERE client_name = 'auto optimizer stats collection';

-- 11g 輸出
CLIENT_NAME                        STATUS
auto optimizer stats collection    ENABLED

二、停用自動收集 (如系統有自定義任務)

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL);
END;
/

-- 驗證
SELECT client_name, status FROM dba_autotask_client
WHERE client_name = 'auto optimizer stats collection';
-- STATUS = DISABLED

三、調整收集時間窗口 (避開業務高峰)

預設：週一至週五 22:00-02:00 (4小時)，週六日全天。

範例：將週一窗口改為 **凌晨 02:00 開始，持續 5 小時**

BEGIN
  DBMS_SCHEDULER.DISABLE(name => '"SYS"."MONDAY_WINDOW"', force => TRUE);
END;
/

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => '"SYS"."MONDAY_WINDOW"',
    attribute => 'DURATION',
    value     => NUMTODSINTERVAL(300, 'MINUTE'));  -- 5小時
END;
/

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => '"SYS"."MONDAY_WINDOW"',
    attribute => 'REPEAT_INTERVAL',
    value     => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0');
END;
/

BEGIN
  DBMS_SCHEDULER.ENABLE(name => '"SYS"."MONDAY_WINDOW"');
END;
/

-- 同理修改 TUESDAY_WINDOW ... SUNDAY_WINDOW

四、查看歷史執行情況

SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed
FROM dba_autotask_client_history
WHERE client_name LIKE '%stats%';

五、手動收集統計信息 (補充場景)

-- 單表
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE', CASCADE => TRUE);

-- 整個 Schema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA', CASCADE => TRUE);

-- 全庫 (慎用)
EXEC DBMS_STATS.GATHER_DATABASE_STATS(CASCADE => TRUE);

六、最佳實踐總結