Oracle 統計信息手動收集與自動收集關閉完整指令集

Oracle 統計信息手動收集與自動收集關閉完整指令集

優化器統計信息是 CBO 生成最優執行計劃的基石。本文匯總 DBMS_STATS 手動收集各類場景指令 (表、分區、並行、採樣、直方圖) 及關閉自動收集的標準流程，供 DBA 日常維護與效能調優直接調用。

一、手動收集統計信息核心指令

1. 基礎表級收集 (最常用)

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  OWNNAME => 'ARDB', TABNAME => 'TRANS_DETAIL',
  ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
  METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',  -- 不建直方圖
  NO_INVALIDATE => FALSE, DEGREE => 8, CASCADE => TRUE);

3. 分區表指定分區收集 (大表維護窗口分批)

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('ARDB', DEGREE => 8, CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_DATABASE_STATS(DEGREE => 8, CASCADE => TRUE);

5. 收集後刷新共用池 (使新統計即時生效)

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;
-- auto optimizer stats collection  DISABLED

三、查看自動任務一週執行排程

  參數推薦值/說明影響
  ESTIMATE_PERCENTDBMS_STATS.AUTO_SAMPLE_SIZE (11g+ 自動決定最佳採樣)平衡精度與速度，大表自動使用全掃描或近似算法
  METHOD_OPTFOR ALL COLUMNS SIZE AUTO (讓 Oracle 決定直方圖) 或 SIZE 1 (無直方圖)直方圖收集極耗資源，僅對傾斜列且 WHERE 條件常用時開啟
  DEGREECPU 核心數 * 0.5 ~ 1 (如 8 核設 4~8)並行收集加速，但增加 CPU/IO 壓力
  GRANULARITYALL (全局+分區+子分區) / PARTITION / SUBPARTITION分區表必須指定，否則預設 GLOBAL ONLY
  NO_INVALIDATEFALSE (立即失效相關游標) / TRUE (延遲失效)FALSE 統計生效快但可能引發硬解析風暴
  CASCADETRUE (同步收集索引統計)必須開啟，索引統計同樣關鍵


五、進階：鎖定/解鎖統計信息 (防止自動任務覆蓋人工優化統計)
SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE OWNER = 'ARDB' AND TABLE_NAME IN ('LOG_TRX','LOG_TRX_DETAIL','ACCT_BALANCE');


核心原則：統計信息要「準」不「全」。大表用 AUTO_SAMPLE_SIZE、直方圖只對「傾斜列+常用 WHERE」開啟、分區表分批收集分區級、收集後 FLUSH SHARED_POOL 並觀察關鍵 SQL 執行計劃變化。鎖定統計是防止自動任務覆蓋人工調優統計的利器，但需建立統計版本管理流程 (Export/Import)。