萬盛學電腦網

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

如何精準鎖定 Oracle 慢 SQL:從 V$SQLAREA 看穿 I/O 與 CPU 瓶頸

兩大類效能問題的成因不同

類型特徵根因定位手段
CPU 密集型佔用大量 CPU、耗時長演算法差、邏輯複雜、缺索引OS 層面(top、vmstat)追蹤到 Session → Module → SQL
I/O 密集型大量物理讀、全表掃描、排序溢寫執行計畫不佳、統計資訊過期、索引缺失查詢 V$SQLAREA 共享池統計

關鍵指標:緩衝區命中率

SELECT executions,
       disk_reads,
       buffer_gets,
       ROUND((buffer_gets - disk_reads) / buffer_gets, 2) AS hit_ratio,
       sql_text
FROM   v$sqlarea
WHERE  executions > 0
AND    buffer_gets > 0
AND    (buffer_gets - disk_reads) / buffer_gets < 0.80   -- 命中率 < 80%
ORDER BY 4 DESC;

命中率的誤區:高不一定好、低不一定壞

案例 A:低命中率但高效

Executions=2, Disk_Reads=6, Buffer_Gets=19, Hit_Ratio=0.68
SQL: SELECT ... FROM emp WHERE empno = :b1  -- 唯一索引範圍掃描

唯一索引幾乎不產生邏輯讀,物理讀 ≈ 邏輯讀 → 命中率低,**但總 I/O 極少,極高效**。

案例 B:高命中率但災難

Executions=2, Disk_Reads=3625, Buffer_Gets=178777, Hit_Ratio=0.98
SQL: 五表 JOIN,其中一表錯走全表掃描

緩衝區命中率 98% 看似完美,實則每次執行 3,600+ 物理讀。調整 SQL 後物理讀降至 <50,命中率反而降至 70%。

真正關鍵:單次執行物理讀

SELECT sql_text,
       executions,
       ROUND(disk_reads / executions, 2) AS reads_per_run,
       disk_reads,
       buffer_gets,
       ROUND((buffer_gets - disk_reads) / buffer_gets, 2) AS hit_ratio
FROM   v$sqlarea
WHERE  executions > 0
AND    buffer_gets > 0
ORDER BY reads_per_run DESC;   -- 關鍵:按單次物理讀降序

輸出範例:

Executions  Reads_Per_Run  Disk_Reads  Buffer_Gets  Hit_Ratio  SQL_Text
----------  -------------  ----------  -----------  ---------  ------------
2           1812.5         3625        178777       0.98       五表 JOIN(需優化)
2           3              6           19           0.68       唯一索引查詢(無需動)

完整排查 SOP

  1. 跑 reads_per_run 查詢,取前 20 筆。
  2. 對每筆 SQL:EXPLAIN PLAN + DBMS_XPLAN.DISPLAY 看執行計畫。
  3. 檢查統計資訊新鮮度(DBMS_STATS)、索引可用性、直方圖。
  4. 改寫 SQL 或加 Hint,重測 reads_per_run 確認下降。
  5. 納入定期巡檢腳本(每日/每週),趨勢圖化。

補充:CPU 密集型 SQL 定位

SELECT s.sid, s.serial#, s.username, s.module,
       ROUND(s.value/100, 2) AS cpu_sec
FROM   v$sesstat s, v$statname n
WHERE  s.statistic# = n.statistic#
AND    n.name = 'CPU used by this session'
AND    s.value > 10000   -- 閾值視環境調整
ORDER BY s.value DESC;

配合 V$SQL 找出該 Session 正在跑的 SQL,再走優化流程。

結語

別被「緩衝區命中率」迷惑。**單次執行物理讀**才是衡量 I/O 效率的黃金指標。建立「每日 Top N 物理讀 SQL」報表,持續優化,資料庫效能自然穩健。

copyright © 萬盛學電腦網 all rights reserved