Oracle 資料庫前瞻性維護：自動工作負載儲存庫 (AWR) 啟用、配置與實戰策略

從「事後諸葛亮」的被動式監控轉向「未雨綢繆」的前瞻性維護，是資料庫管理員進階的關鍵。Oracle 10g 引入的 **自動工作負載儲存庫 (AWR, Automatic Workload Repository)** 透過背景進程 MMON/MMNL 自動收集效能統計，構建歷史基線，支援主動發現效能退化徵兆。本文詳細解析 AWR 架構、三大統計層級、保留/截取間隔配置、空間管理與實戰運營策略。

一、 被動 vs 前瞻性監控對比

二、 AWR 核心架構

• MMON (Manageability Monitor)：主進程，預設每 **60 分鐘** 喚醒，收集統計 → 寫入 AWR 表 (SYS 用戶擁有，儲於 SYSAUX 表空間)
• MMNL (Manageability Monitor Lite)：輕量輔助進程，負責 Active Session History (ASH) 緩衝區沖刷至磁碟
• 儲存對象：WRH$_* 系列表 (如 WRH$_SYSSTAT, WRH$_SQLSTAT)，受 DBMS_WORKLOAD_REPOSITORY 管理

三、 統計收集層級

設定指令

-- Session 級 (立即生效)
ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;

-- 系統級 (持久化，需 SPFILE)
ALTER SYSTEM SET STATISTICS_LEVEL = TYPICAL SCOPE = BOTH;

四、 快照保留與截取間隔配置

預設：保留 **7 天 (10080 分鐘)**，截取間隔 **60 分鐘**。

單位均為分鐘，常見誤區：以天為單位導致保留極短。

修改範例

-- 保留 30 天 (43200 分鐘)，截取間隔 30 分鐘
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
  retention => 43200,   -- 30 * 24 * 60
  interval  => 30);

-- 僅修改保留期
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 20160); -- 14 天

-- 僅修改間隔
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 15); -- 15 分鐘

配置決策指南

五、 空間管理與清理策略

• 自動清除：MMON 依 retention 定期清除過期快照
• 手動清除：

  EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 100, high_snap_id => 200);

• 匯出歸檔 (長期保留不佔 SYSAUX)：

  -- 建立匯出表
  EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(
    start_time => SYSTIMESTAMP - 30, end_time => SYSTIMESTAMP,
    baseline_name => 'BASELINE_30D', template_name => 'TEMPLATE_30D',
    expiration => 365);  -- 保留 1 年
  
  -- 或使用 Data Pump 匯出 AWR 表
  EXPDP system/password DIRECTORY=dp_dir DUMPFILE=awr_202401.dmp TABLES=WRH$_SYSSTAT,WRH$_SQLSTAT,...

• 監控 SYSAUX 使用量：

  SELECT occupant_name, space_usage_kbytes/1024 MB
  FROM   v$sysaux_occupants
  WHERE  occupant_name LIKE 'AWR%';

六、 AWR 報表實戰運用

-- 產生 HTML 報表 (需指定 begin/end snap_id)
@?/rdbms/admin/awrrpt.sql

-- 產生特定 SQL 歷史報表
@?/rdbms/admin/awrsqrpt.sql

-- 產生差異報表 (比較兩個時段)
@?/rdbms/admin/awrddrpt.sql

報表關鍵關注區塊

1. Load Profile：每秒交易/邏輯讀/物理讀/Redo 大小 → 識別負載變化
2. Top 5 Timed Events：等待事件佔比 → 定位瓶頸類型 (CPU/IO/鎖/鎖閂)
3. SQL Statistics：Elapsed Time/Executions/Buffer Gets/Rows Processed → 鎖定惡性 SQL
4. Instance Efficiency Percentages：Buffer Hit% / Library Hit% / Execute to Parse% → 診斷記憶體/解析效率
5. Segment Statistics：Logical/Physical Reads per Segment → 找出熱點表/索引

七、 企業級前瞻性維護作業流程

1. 每日晨檢：跑前日 AWR 差異報表 (對比同期基線)，關注 Top Events/Load Profile 變化
2. 每週趨勢分析：匯出 4 週 AWR 關鍵指標 (DB Time, Logical Reads, Redo Size, Top SQL) 繪製趨勢圖
3. 變更前基線：重大變更 (統計收集、參數調整、程式上線) 前擷取 AWR 基線
4. 變更後驗證：變更後對比基線，確認無退化再簽核上線
5. 容量規劃：依儲存/記憶體/CPU 趨勢預測 3~6 個月擴容時間點

八、 避坑指南

• ❌ STATISTICS_LEVEL=BASIC 生產環境 → 失去前瞻能力
• ❌ Retention 設天數而非分鐘 → 實際僅保留幾分鐘
• ❌ Interval 設過短 (如 5 分鐘) + Retention 過長 → SYSAUX 爆滿導致資料庫掛起
• ✅ 定期匯出歷史基線 釋放 SYSAUX 空間
• ✅ 結合 ASH (V$ACTIVE_SESSION_HISTORY) 秒級分析 補足 AWR 分鐘級盲區

AWR 是前瞻性維護的「飛行記錄儀」，正確配置保留/間隔、建立基線比較機制、納入日常運營流程，才能真正實現「故障前發現、變更後驗證、容量早規劃」的運維境界。

1. 上一篇文章： Oracle 體系架構雙核心：資料庫 (靜態檔案) 與實例 (動態記憶體/進程) 完整對照
2. 下一篇文章： AWR 快照保留與截取間隔深度配置：分鐘單位陷阱、空間膨脹風險與最佳實務

Oracle 資料庫基礎

Oracle Instant Client 免安裝部署全指南：從下載到連線驗證

前言 完整 Oracle Client 安裝包動輒數 GB，

Oracle 綁定變數效能實測：EXECUTE IMMEDIATE 綁定 vs 字串拼接

實驗設計 向表 T(X INT) 插入 1,000 筆資料，

Oracle TRIM/LTRIM/RTRIM 函數深度解析：字元移除規則與常見誤區

TRIM 三種模式 TRIM([LEADING | TRAI

Oracle 9i PL/SQL 例外處理實戰：系統預定義與自訂例外的完整範例指南

Oracle 9i 例外處理兩大類別 系統預定義例外： 編