Oracle AWR 報告解讀：SQL Ordered by Gets/Reads 定位 CPU/IO 瓶頸

Oracle AWR 報告解讀：SQL Ordered by Gets/Reads 定位 CPU/IO 瓶頸

AWR 報告中 SQL Ordered by Gets (邏輯讀/Buffer Gets) 反映 CPU 壓力來源；SQL Ordered by Reads (物理讀/Physical Reads) 反映 I/O 壓力來源。兩大榜單是效能調優的「聚光燈」。

案例一：CPU 被單一 SQL 打滿 (Gets 榜單)

SQL Ordered by Gets
Total Buffer Gets: ###############
Captured SQL account for 5.0% of Total

Buffer Gets   Executions  Gets/Exec  %Total  CPU Time(s)  Elapsed Time(s)  SQL Id        Module         SQL Text
1,606,380,390 84          19,123,576  -0.84   37,923.62    207,407.54       78tpfukr4m00p  JDBC Thin Client SELECT SUM(temp.MidUser) ...
1,479,957,445 0           -           -0.77   26,647.48    43,778.62        2mxgaus0t6pxz  JDBC Thin Client SELECT 設備ID ...
1,280,365,092 73          17,539,247  -0.67   30,263.02    159,789.71       2qrj96dwj2xx1  JDBC Thin Client SELECT SUM(temp.MidUser) ...
...

分析：前幾條 SQL 佔據絕大多數 Buffer Gets，Gets/Exec 極高 (千萬級)，CPU Time 接近 Elapsed Time，顯示純 CPU 運算瓶頸 (無 I/O 等待)。

對策：

• 優化 SQL：檢查執行計畫、補充索引、改寫關聯、使用分區裁剪。
• 調整 OPTIMIZER_MODE、統計資訊收集頻率。
• 若為報表類大查詢，考慮離線跑批、物化視圖預聚合。

案例二：I/O 子系統達到極限 (Reads 榜單 + 等待事件)

Top 5 等待事件中 direct path read 佔比極高。Oracle 11g 引入 _SMALL_TABLE_THRESHOLD (預設 2% * DB_CACHE_SIZE) 判定大表是否走 Direct Path Read 繞過 Buffer Cache。

對策：

• 優化高 Reads SQL (同上)。
• 擴大 SGA / Buffer Cache，提高快取命中率。
• 調整 _SMALL_TABLE_THRESHOLD (隱藏參數，需諮詢 Oracle Support) 或升級至 12c+ 使用 DB_BIG_TABLE_CACHE_PERCENT_TARGET 自動管理大表快取。
• 存儲層面：更換 SSD、調整 RAID 級別、啟用儲存陣列快取。

快速定位腳本

-- 即時查詢高消耗 SQL (需診斷包授權)
SELECT sql_id, executions, buffer_gets, disk_reads,
       buffer_gets/executions AS gets_per_exec,
       cpu_time/1e6 AS cpu_sec, elapsed_time/1e6 AS ela_sec
FROM v$sqlstats
WHERE executions > 0
ORDER BY buffer_gets DESC
FETCH FIRST 20 ROWS ONLY;

-- 對應物理讀
ORDER BY disk_reads DESC;

核心觀念

• Gets = CPU：邏輯讀在記憶體完成，消耗 CPU 週期。高 Gets 必引高 CPU。
• Reads = I/O：物理讀觸發磁碟存取，消耗 I/O 頻寬與延遲。高 Reads 必引 I/O 瓶頸。
• Gets/Exec：單次執行掃描量，若極大說明全表掃描、笛卡爾積、缺少索引。
• %Total：佔總資源比例，優先處理前 3~5 名通常能解決 80% 問題 (二八定律)。

定期審閱 AWR Gets/Reads 榜單，是資料庫預防性維護的核心動作。