Oracle dbms_stats統計信息管理全攻略：收集、刪除、匯出入、鎖定與恢復

Oracle dbms_stats 統計信息管理企業級實戰指南

核心收集程序

• GATHER_INDEX_STATS - 索引統計
• GATHER_TABLE_STATS - 表、列、索引統計
• GATHER_SCHEMA_STATS - Schema級統計
• GATHER_DATABASE_STATS - 全庫統計

GATHER_TABLE_STATS 關鍵參數詳解

DBMS_STATS.GATHER_TABLE_STATS(
  ownname VARCHAR2,           -- 表擁有者
  tabname VARCHAR2,           -- 表名
  partname VARCHAR2 DEFAULT NULL,     -- 分區名
  estimate_percent NUMBER DEFAULT AUTO_SAMPLE_SIZE,  -- 採樣比例 0.000001-100
  block_sample BOOLEAN DEFAULT FALSE, -- 塊採樣替代行採樣
  method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE AUTO', -- 直方圖策略
  degree NUMBER DEFAULT NULL,         -- 並行度
  granularity VARCHAR2 DEFAULT 'AUTO', -- 分區統計粒度
  cascade BOOLEAN DEFAULT FALSE,      -- 是否收集索引統計
  stattab VARCHAR2 DEFAULT NULL,      -- 統計表名(不指定寫數據字典)
  statid VARCHAR2 DEFAULT NULL,       -- 統計標識
  statown VARCHAR2 DEFAULT NULL,      -- 統計表擁有者
  no_invalidate BOOLEAN DEFAULT TRUE, -- 不立即失效游標
  force BOOLEAN DEFAULT FALSE);       -- 強制收集即使表鎖定

實戰案例集

案例1：表+所有列+所有索引 (採樣自動、僅索引列直方圖、並行度10)

BEGIN DBMS_STATS.GATHER_TABLE_STATS(
  ownname=>'JINRILOG', tabname=>'ORDERLOG',
  estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt=>'FOR ALL INDEXED COLUMNS', cascade=>TRUE, degree=>10); END; /

案例2：僅表統計 (15%採樣、無直方圖、不收集索引)

BEGIN DBMS_STATS.GATHER_TABLE_STATS(
  ownname=>'SCOTT', tabname=>'TT', estimate_percent=>15,
  method_opt=>'FOR TABLE', cascade=>FALSE); END; /

案例3：指定列不收集直方圖 (SIZE 1)

BEGIN DBMS_STATS.GATHER_TABLE_STATS(
  ownname=>'SCOTT', tabname=>'TT', estimate_percent=>100,
  method_opt=>'FOR COLUMNS SIZE 1 OBJECT_NAME OBJECT_ID', cascade=>FALSE); END; /

案例4：全收集 (100%採樣、含索引)

BEGIN DBMS_STATS.GATHER_TABLE_STATS(
  ownname=>'SCOTT', tabname=>'TT', estimate_percent=>100, cascade=>TRUE); END; /

案例5：刪除表統計

BEGIN DBMS_STATS.DELETE_TABLE_STATS(ownname=>'SCOTT', tabname=>'TT'); END; /

Schema級統計收集策略

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
  ownname=>'SCOTT', options=>'GATHER AUTO',
  estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt=>'FOR ALL COLUMNS SIZE REPEAT', degree=>15);

options參數：GATHER(全量)、GATHER EMPTY(無統計對象)、GATHER STALE(修改超10%)、GATHER AUTO(空+過期)。
method_opt進階：SKEWONLY(僅傾斜列)、REPEAT(重複上次)、AUTO(自動決定)。

統計信息傳輸與備份恢復

創建統計表並匯出/匯入

-- 1. 創建統計表
BEGIN DBMS_STATS.CREATE_STAT_TABLE(ownname=>'JINRILOG', stattab=>'STAT_TABLE'); END; /

-- 2. 收集並匯出到統計表
BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname=>'JINRILOG', tabname=>'ORDERLOG'); END; /
BEGIN DBMS_STATS.EXPORT_TABLE_STATS(ownname=>'JINRILOG', tabname=>'ORDERLOG', stattab=>'STAT_TABLE'); END; /

-- 3. 目標庫匯入
BEGIN DBMS_STATS.IMPORT_TABLE_STATS(ownname=>'JINRILOG', tabname=>'ORDERLOG', stattab=>'STAT_TABLE'); END; /

統計信息版本管理與PENDING機制

修改保留時間與恢復歷史版本

-- 查看可恢復時間點
SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

-- 恢復Schema到指定時間
EXEC DBMS_STATS.RESTORE_SCHEMA_STATS(
  ownname=>'SCOTT', as_of_timestamp=>'28-1月-15 02.10.00.252146000 下午 +08:00',
  no_invalidate=>FALSE);

PENDING統計防患未然

-- 1. 設置表級PENDING模式 (新統計不自動發布)
EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'T1', 'PUBLISH', 'FALSE');

-- 2. 收集統計 (進入PENDING狀態，數據字典不變)
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'T1');

-- 3. 測試新統計效能
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;
-- 執行計劃測試...

-- 4. 驗證通過發布
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SCOTT', 'T1');

-- 5. 或刪除廢棄
EXEC DBMS_STATS.DELETE_PENDING_STATS('SCOTT', 'DEPT');