利用 AWR 報告分析解決 Oracle CPU 過高問題完整案例
AWR 核心概念
- AWR (Automatic Workload Repository)：Oracle 10g+ 內建效能診斷基礎設施
- GATHER_STATS_JOB：預設每小時收集一次快照，保留 8 天
- 收集內容：等待事件、系統/會話級統計、高資源消耗 SQL 等
- 可手動建立快照（測試前後）標記關鍵時間點
AWR 報告手動產生步驟
- 以 SYSDBA 登入 sqlplus
- 執行
@?/rdbms/admin/awrrpt.sql
- 選擇報告類型（text/html）、天數範圍、起始/結束 snap_id、報告名稱
- 報告輸出於 sqlplus 啟動目錄
案例背景
- 某交易場景，資料庫 CPU 平均使用率近 90%
- 12 Core 伺服器，Oracle 佔服務器 CPU 89.9% 的 99.5%
分析過程詳解
1. 報表頭與關鍵效能指標
檢視 Buffer Nowait、Buffer Hit、Library Hit、Execute to Parse、Parse CPU to Parse Elapsd、Redo NoWait 等比率，快速判斷瓶頸類型。
2. Time Model Statistics
DB Time: 15499 秒
SQL Execute Elapsed Time: 15499 秒 (100%)
Parse Time Elapsed: 26 秒 (0.17%)
Hard Parse Elapsed Time: 26 秒 (100% of parse)
解析時間極短，硬解析佔解析 100% 但總量微小 → 解析非瓶頸，問題在資料存取階段。
3. 負載概要
資料庫層面 TPS 僅 1.3 → 極低吞吐，大量 CPU 消耗在少量查詢上。
4. SQL Ordered by CPU Time
鎖定兩個高 CPU SQL：
SQL_ID: cjnzff37c9gr3SQL_ID: cq32agu0qkpd8
執行次數多、單次 CPU 高。
5. 執行計劃深度分析
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
-- 輸入上述 SQL_ID 取得詳細計劃
發現 CTR_LOAN_CONT 表存取 Cost 極高，索引範圍掃描需讀 196,000 行。該表 1,200+ 萬行、5888MB，統計資訊較新。
6. Segments by Logical Reads
96% 邏輯讀集中於 CTR_LOAN_CONT 表 → 確認核心熱點。
優化措施
- 邏輯下推至應用層：將資料庫中的複雜 SQL 判斷邏輯移至應用程式實現，降低資料庫計算負載
- 視圖優化：保留視圖結構，但新增第三方平台過濾條件；將
UNION（排序去重）改為 UNION ALL（無排序去重）
優化成果
| 指標 | 優化前 | 優化後 | 改善幅度 |
|---|
| CTR_LOAN_CONT 表存取 Cost | 15,359 | 276 | 降低 98.2% |
| 資料庫 CPU 平均使用率 | 89.9% | 11.6% | 降低 87% |
AWR 分析標準化檢查清單
- Report Header：快照時間、資料庫版本、負載概要
- Load Profile：TPS、邏輯讀/物理讀/重做生成速率
- Instance Efficiency Percentages：命中率、解析比率
- Top 5 Timed Events：等待事件佔比
- SQL Ordered by CPU/Elapsed/Buffer Gets：高消耗 SQL 識別
- Segments by Logical/Physical Reads：熱點物件定位
- Time Model：DB Time 組成結構
- 執行計劃深度分析：索引使用、連接順序、Cardinality 準確度
工具補充
awrsqrpt.sql：單一 SQL 詳細報告awrddrpt.sql：兩段快照差異報告（Period Comparison）- Enterprise Manager (EM) 圖形化介面
