善用 AWR 報告診斷 Oracle CPU 過高：從症狀到根因的完整調優實戰

AWR 核心價值與生成方式

AWR (Automatic Workload Repository) 是 Oracle 10g+ 內建效能診斷引擎，每小時自動擷取系統快照，預設保留 8 天。透過比較不同時段快照，可精準定位效能瓶頸。

手動生成 AWR 報告步驟

-- 直接指定參數生成 HTML 報告
@?/rdbms/admin/awrrpt.sql html 1 100 105 awr_report_100_105.html

案例實戰：某交易系統 CPU 近 90% 根因分析

環境資訊

• 12 Core 伺服器
• Oracle 佔系統 CPU 89.9%，資料庫內部 CPU 佔比 99.5%
• Instance CPU 圖表顯示持續高載

分析步驟與關鍵發現

1. 報告頭資訊：檢視 Buffer Hit%、Library Hit%、Execute to Parse 等關鍵指標皆正常，無明顯記憶體壓力。
2. Time Model Statistics：
   • SQL Execute Elapsed Time: 15,499 秒
   • Parse Time Elapsed: 26 秒
   • Hard Parse Elapsed Time: 26 秒
   • 結論：解析非瓶頸，問題在資料存取階段。
3. 負載概要：資料庫層面 TPS 僅 1.3，極低，說明高 CPU 非來自高交易量。
4. SQL Ordered by CPU Time：鎖定兩支高耗 SQL：
   • SQL_ID: cjnzff37c9gr3
   • SQL_ID: cq32agu0qkpd8
   • 執行次數多、單次 CPU 高、累計 CPU 佔比極大。
5. 執行計畫深度分析 (awrsqrpt.sql)：
   • 核心表 CTR_LOAN_CONT (1200+ 萬筆、5888MB)
   • 索引範圍掃描預估讀取 196,000 行
   • Cost 高達 15,359
   • 統計資訊較新，非統計資訊問題。
6. Segments by Logical Reads：96% 邏輯讀集中於 CTR_LOAN_CONT 表，確認熱點。

根因結論

SQL 撰寫方式導致大量不必要的資料存取，而非資源不足。具體為：視圖使用 UNION (含排序去重) 且缺乏有效過濾條件，導致全表大量掃描。

調優措施與成效

措施 1：邏輯下推至應用層

將資料庫層的複雜判斷邏輯 (CASE/DECODE/子查詢) 移至應用程式處理，降低資料庫 CPU 負載。

措施 2：視圖優化 - UNION 改 UNION ALL + 增加過濾條件

指標調優前調優後改善幅度
CTR_LOAN_CONT 表存取 Cost15,359276降低 98.2%
資料庫伺服器 CPU 平均使用率89.9%11.6%降低 87%
SQL 執行經過時間秒級~分級毫秒級數量級提升


AWR 分析黃金法則

先看宏觀：報告頭、Time Model、Load Profile 判斷瓶頸類型 (CPU/IO/鎖/鎖)。
再看微觀：Top SQL (by CPU/Elapsed/Reads) 定位具體語句。
深挖計畫：awrsqrpt 取得實際執行計畫，檢視 Access Path、Cardinality、Cost。
關聯段統計：Segments by Logical/Physical Reads 確認熱點物件。
驗證統計資訊：確認相關表/索引統計資訊新鮮度。


常用 AWR 腳本速查

腳本
用途
awrrpt.sql
標準 AWR 報告 (HTML/Text)
awrsqrpt.sql
指定 SQL_ID 的執行計畫報告
awrddrpt.sql
兩段快照差異報告 (Period 比較)
awrgrpt.sql
RAC 叢集全域 AWR 報告
awrinfo.sql
匯出 AWR 資料供離線分析


總結
AWR 是 Oracle 效能診斷的「核磁共振」。本案例展示從現象 (CPU 高) → 定位 (Top SQL) → 根因 (計畫劣化/邏輯冗餘) → 措施 (SQL 改寫/邏輯下推) → 驗證 (Cost/CPU 大幅下降) 的完整閉環。掌握 AWR 系統性分析方法，是 DBA 從「救火」邁向「預防醫學」的關鍵。