Oracle 邏輯讀/物理讀資源佔用 Top 10 SQL 查詢腳本

Oracle 邏輯讀/物理讀資源佔用 Top 10 SQL 查詢腳本

從 V$SQLAREA 找出邏輯讀 (Buffer Gets) 與物理讀 最高的 SQL，是效能調優的第一步。本文提供標準 Top 10 查詢腳本。

邏輯讀 Top 10 (Buffer Gets/Execution)

SET LINESIZE 300 PAGESIZE 300
SET LONG 50000

SELECT *
FROM (
  SELECT sql_fulltext AS sql,
         sql_id,
         buffer_gets / executions AS "Gets/Exec",
         buffer_gets,
         executions
  FROM v$sqlarea
  WHERE buffer_gets > 10000
    AND executions > 0
  ORDER BY "Gets/Exec" DESC
)
WHERE ROWNUM <= 10;

物理讀 Top 10 (Disk Reads/Execution)

SET LINESIZE 300 PAGESIZE 300
SET LONG 50000

SELECT *
FROM (
  SELECT sql_fulltext AS sql,
         sql_id,
         disk_reads / executions AS "Reads/Exec",
         disk_reads,
         executions
  FROM v$sqlarea
  WHERE disk_reads > 1000
    AND executions > 0
  ORDER BY "Reads/Exec" DESC
)
WHERE ROWNUM <= 10;

關鍵指標說明

Session 級即時邏輯/物理讀查詢

SELECT t.sid, n.name, t.value
FROM v$sesstat t, v$statname n
WHERE t.statistic# = n.statistic#
  AND n.statistic# IN (9, 54)  -- 9: session logical reads, 54: physical reads
  AND t.sid = &sid;

資源佔用綜合視圖 (v$resource_limit)

SELECT * FROM v$resource_limit
WHERE resource_name IN ('processes','sessions','enqueue_locks','dml_locks','transactions');

調優建議流程

1. 跑 Top 10 SQL 腳本，鎖定高消耗 SQL_ID
2. SELECT * FROM v$sql_plan WHERE sql_id = '...' 看執行計畫
3. 檢查：全表掃描、缺索引、統計過期、笛卡兒積、不當 Hint
4. 優化：建索引、改寫 SQL、收集統計、調整參數、分區裁剪
5. 驗證：對比優化前後 Gets/Exec、Reads/Exec、Elapsed Time

注意事項

• V$SQLAREA 為共享游標統計，隨 Shared Pool 洗出而重置
• 長期分析請用 AWR/ASH 報告 (DBMS_WORKLOAD_REPOSITORY)
• 閾值 buffer_gets > 10000、disk_reads > 1000 視系統規模調整
• 濾除 executions = 0 避免除零錯誤