萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle AWR 報告分析實戰方法論:從整體到細節的系統化診斷流程

前言

AWR(Automatic Workload Repository)報告動輒上百頁,若從頭細讀不僅耗時且易迷失方向。資深 DBA 採用「整體優先、對比為主、跳躍式深入、業務語境判斷」的四步驟分析法,能在最短時間定位效能瓶頸。本文系統整理 AWR 資料來源、快照管理、報告產生,並詳細拆解「系統整體狀況」與「具體狀況」兩大分析維度的關鍵指標與解讀邏輯。

一、AWR 資料來源與快照機制

1.1 兩大資料來源

  1. 記憶體即時統計:儲於 SGA 中,透過 V$ 動態視圖查詢(如 V$SYSSTAT、V$SYSTEM_EVENT)。
  2. 快照持久化:MMON 背景程序定期(預設每小時)將記憶體統計寫入 SYSAUX 表空間的 DBA_HIST_* 表群,保留 7 天(預設)。

1.2 快照設定查詢與調整

-- 查詢目前設定
SELECT snap_interval, retention FROM dba_hist_wr_control;
-- 輸出範例:+00000 01:00:00.0  |  +00007 00:00:00.0  (每小時、保留7天)

-- 修改為每 30 分鐘一個快照,保留 10 天(以分鐘為單位)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 30, RETENTION => 10*24*60);

-- 手動建立快照(如重大變更前後)
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

建議:高載系統可縮短間隔至 15~30 分鐘;長期保留需評估 SYSAUX 空間增長。

二、AWR 報告產生方式

-- 以 SYSDBA 執行
@?/rdbms/admin/awrrpt.sql
-- 依提示輸入:報告類型、快照 ID 範圍、輸出檔名

亦可使用 awrrpti.sql(指定 Instance)、awrgrpt.sql(RAC 全叢集)、awrsqrpt.sql(單一 SQL 專項)。

三、AWR 分析策略:四大原則

  1. 整體優先:先看摘要區,建立系統健康基線,再決定深入方向。
  2. 對比為主:拿「正常時段」與「問題時段」報告並列比對,找差異而非看絕對值。
  3. 跳躍式深入:根據關注點跳轉章節,避免陷入細節泥沼。
  4. 業務語境判斷:同一指標在 OLTP 與 DW 系統含義迥異,必須結合業務模式解讀。

四、系統整體狀況分析

4.1 Load Profile(負載概況)

核心指標與解讀:

指標關注重點異常徵兆
Per Second / Per Transaction交易吞吐量基線驟降或飆升,對應業務流量異常
Physical Reads/Writes磁碟 I/O 強度Physical Reads 高於 Logical Reads 10%+ 表示緩衝區命中率低
Logical Reads/Writes記憶體存取量單筆交易 Logical Reads 過高 → SQL 效率差、缺索引
Parses (Hard/Total)解析負擔Hard Parse 佔比 > 10% → 未使用 Bind Variable、Cursor Sharing 問題
Logons連線建立頻率高頻短連 → 應用未用連線池

4.2 Instance Efficiency Percentages(實例效率百分比)

各指標應逼近 100%,兩項例外:

  • Execute to Parse % ≥ 70%:低於此值表示過度解析,檢查 Cursor Sharing、Bind Variable 使用。
  • Parse CPU to Parse Elapsed %:解析 CPU 佔解析總時間比例,過低表示解析等待(如 Library Cache 鎖競爭)。

黃金法則:全部達標 ≠ 系統無問題;但任一顯著偏離 ≡ 必有效能問題。

五、具體狀況深入分析

5.1 Top 5/10 Timed Events(前 N 大等待事件)

依「總等待時間」排序,直接指向瓶頸類型:

等待事件典型成因檢查方向
db file sequential read索引範圍掃描、單區塊讀取索引效率、Clustering Factor、熱塊競爭
db file scattered read全表掃描、多區塊讀取缺索引、統計資訊過期、執行計劃錯誤
log file syncCommit 等待 LGWR 寫入 RedoRedo Log 檔案/裝置效能、Commit 過於頻繁
log file switch completionLog Switch 等待歸檔/檢查點完成Redo Log 群組/大小不足、歸檔延遲
enq: TX - row lock contention列鎖競爭熱點資料、長交易、索引缺失導致全表掃描鎖定過多列
latch: cache buffers chains熱門資料區塊競爭熱點表/索引、Nested Loop Join 過度、統計資訊偏差

5.2 Top N SQL(依各維度排序)

  • Elapsed Time:總耗時最長 → 整體資源消耗大戶。
  • CPU Time:純計算密集型 SQL。
  • Buffer Gets:邏輯讀最多 → 可能為全表掃描或低效連接。
  • Disk Reads:物理讀最多 → I/O 密集、快取未命中。
  • Executions:執行次數極高 → 即使單次輕量,累積影響大。

對目標 SQL:擷取 SQL_ID → DBMS_XPLAN.DISPLAY_AWR 取得實際執行計劃 → 分析 Cardinality/Access Path/Join Order/Partition Pruning。

5.3 RAC 專項指標(僅 RAC 環境)

  • Global Cache CR/Current Block Receive Time:跨節點區塊傳輸延遲。
  • GC CR Block Lost / Busy:全域緩衝區衝突。
  • Interconnect Latency:私有網路延遲(應 < 1ms)。
  • Service/Instance 級負載分布:是否均衡,避免單節點熱點。

5.4 SGA/PGA 記憶體分析

  • Buffer Cache Hit Ratio:< 95% 需檢查熱點資料是否被擠出、DB_CACHE_SIZE 是否足夠。
  • Shared Pool / Library Cache Hit Ratio:Hard Parse 高時優先檢查。
  • PGA Aggregate Target / Cache Hit %:排序/雜湊作業是否落盤,調整 PGA_AGGREGATE_TARGET 或啟用 MEMORY_TARGET 自動管理。

5.5 表空間與資料檔 I/O

  • 檢視 Tablespace I/O Stats:Read/Write 延遲、吞吐量、熱門表空間識別。
  • 結合 DBA_HIST_FILESTATXS 追蹤歷史 I/O 趨勢,規劃檔案佈局(分離 Data/Index/Temp/Undo/Redo 至不同磁碟/ASM Disk Group)。

六、分析工作流程速查表

  1. 取得「正常基線」與「問題時段」兩份 AWR。
  2. 比對 Load Profile:吞吐量、I/O、Parse 特徵是否異常。
  3. 比對 Instance Efficiency:找出偏離指標鎖定問題域。
  4. 閱讀 Top Timed Events:定位前 3 大等待類型。
  5. 針對前 3 事件,查找 Top SQL 與 Segment Statistics 關聯物件。
  6. 擷取關鍵 SQL_ID → 分析執行計劃、統計資訊、索引狀態。
  7. 若為 RAC,檢查 RAC Statistics 與 Interconnect。
  8. 檢查 Memory/Undo/Temp/Redo 區域是否有配置不足跡象。
  9. 產出分析報告:現象、證據、根因、建議動作、預期效益、驗證方式。

結語

AWR 分析不是讀報告,而是「用報告做診斷」。建立標準化分析流程、累積基線報告庫、善用 DBMS_WORKLOAD_REPOSITORY 程式化比對,能將效能調優從「經驗驅動」轉型為「數據驅動」。下一步可進階學習 ASH(Active Session History)報告進行秒級鑽取,以及 SQL Tuning Advisor 自動化建議的輔助應用。

copyright © 萬盛學電腦網 all rights reserved