Oracle 統計信息收集與還原實戰案例：DBMS_STATS 完整流程

Oracle 統計信息收集與還原實戰案例：DBMS_STATS 完整流程

統計信息是 CBO（基於成本優化器）生成優質執行計劃的基石。但統計信息重新收集後，有時會導致計劃劣化。Oracle 提供 DBMS_STATS 包支持統計信息的歷史保留與還原，本文給出標準化操作流程與實戰腳本。

標準化處理流程

1. 重新收集統計信息（GATHER_TABLE_STATS）。
2. 對比執行計劃（EXPLAIN PLAN / V$SQL_PLAN），確認是否回歸優質計劃。
3. 若計劃改善，Kill 相關會話並重跑作業。
4. 若計劃惡化，還原該表最近一次的統計信息（RESTORE_TABLE_STATS）。

獲取執行計劃通用腳本（getplan.sql）

SET PAGESIZE 0 LINESIZE 150 SERVEROUTPUT ON SIZE 10000
COL plan_table_output FORMAT A125
UNDEFINE hash_value
SET VERIFY OFF FEEDBACK OFF
VAR hash_value VARCHAR2(20)
BEGIN :hash_value := '&hash_value'; END;
/
INSERT INTO plan_table (statement_id, timestamp, operation, options, object_node,
  object_owner, object_name, optimizer, search_columns, id, parent_id, position,
  cost, cardinality, bytes, other_tag, partition_start, partition_stop,
  partition_id, other, distribution, cpu_cost, io_cost, temp_space,
  access_predicates, filter_predicates, plan_id, object_alias, depth,
  projection, time, qblock_name)
SELECT DISTINCT :hash_value, SYSDATE, operation, options, object_node,
  object_owner, object_name, optimizer, search_columns, id, parent_id, position,
  cost, cardinality, bytes, other_tag, partition_start, partition_stop,
  partition_id, other, distribution, cpu_cost, io_cost, temp_space,
  access_predicates, filter_predicates, :hash_value, object_alias, depth,
  projection, time, qblock_name
FROM v$sql_plan WHERE hash_value = :hash_value;
COL piece NOPRINT
SELECT DISTINCT piece, sql_text FROM v$sqltext WHERE hash_value = :hash_value ORDER BY piece;
@?/rdbms/admin/utlxplp.sql
SET LINESIZE 80 VERIFY ON FEEDBACK ON PAGESIZE 1000

實戰案例步驟

Step 1：獲取目標 SQL_ID 與計劃哈希值

SELECT b.begin_interval_time, a.sql_id, a.plan_hash_value
FROM dba_hist_sqlstat a, dba_hist_snapshot b
WHERE a.snap_id = b.snap_id
  AND a.sql_id = '<SQL_ID>'
ORDER BY 1;

Step 2：獲取當前執行計劃

SQL> @getplan
Enter value for hash_value: 684487124
...
| Id | Operation                    | Name            | Rows | Bytes | Cost | Time |
|  0 | UPDATE STATEMENT             |                 |      |       |    1 |      |
|  1 |  UPDATE                      | MGMT_TARGETS    |      |       |      |      |
|  2 |   TABLE ACCESS BY INDEX ROWID| MGMT_TARGETS    |    1 |   182 |    1 | 0:1  |
|  3 |    INDEX UNIQUE SCAN         | MGMT_TARGETS_IDX|    1 |       |    0 |      |
Predicate: 2 - filter("LAST_LOAD_TIME" < :B2 OR ...), 3 - access("TARGET_GUID"=:B1)

Step 3：重新收集統計信息（並行、全採樣、含分區）

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => '<TABLE_OWNER>',
    tabname          => '<TABLE_NAME>',
    degree           => 8,
    method_opt       => 'FOR ALL COLUMNS SIZE 1',
    cascade          => TRUE,
    estimate_percent => 1,          -- 100% 採樣
    granularity      => 'GLOBAL AND PARTITION',
    no_invalidate    => FALSE       -- 立即失效相關游標
  );
END;
/
-- 關鍵參數說明：
-- method_opt 'FOR ALL COLUMNS SIZE 1'：僅收集基本列統計，不創建直方圖，避免綁定變量窺探問題
-- granularity 'GLOBAL AND PARTITION'：同時收集全局與分區級統計
-- no_invalidate FALSE：立即讓相關 SQL 重解析，驗證新統計效果

Step 4：再次獲取計劃對比

執行 @getplan，檢查 OPERATION、COST、CARDINALITY 是否趨向合理。

Step 5：統計信息還原（若新統計導致劣化）

BEGIN
  DBMS_STATS.RESTORE_TABLE_STATS(
    ownname      => '<TABLE_OWNER>',
    tabname      => '<TABLE_NAME>',
    as_of_timestamp => TIMESTAMP'2024-01-15 10:30:00', -- 指定還原時間點
    no_invalidate => FALSE
  );
END;
/
-- 也可還原方案/數據庫/字典統計：
-- RESTORE_SCHEMA_STATS / RESTORE_DATABASE_STATS / RESTORE_DICTIONARY_STATS

關鍵視圖與保留策略

• DBA_TAB_STATS_HISTORY：查看表統計收集歷史與時間戳。
• DBA_OPTSTAT_OPERATIONS：統計操作審計日志。
• 默認保留 31 天（DBMS_STATS.ALTER_STATS_HISTORY_RETENTION 可調整）。

最佳實踐建議

1. 生產環境禁止無計劃的全庫 GATHER_SCHEMA_STATS，應按表級精細控制。
2. 重要表設置 LOCK_TABLE_STATS 鎖定統計，配合定時任務窗口收集。
3. 收集前先 EXPORT_TABLE_STATS 導出備份，提供秒級回滾能力。
4. 監控 DBA_TAB_MODIFICATIONS，僅在修改量超過閾值（如 10%）時觸發收集。

統計信息管理的核心不是「收集」，而是「可控、可追溯、可回滾」。建立標準化流程，才能在計劃劣化時從容應對。