Oracle 惡劣 SQL 識別與分析實用查詢集

核心原則

定期（如每月）在離峰時段執行分析查詢，避免增加生產負載。根據實際系統特徵調整閾值，切勿生搬硬套。

高資源消耗 SQL 識別查詢

1. 邏輯讀取過高 (Buffer Gets > 500,000)

SELECT * FROM (
    SELECT buffer_gets, sql_text
    FROM v$sqlarea
    WHERE buffer_gets > 500000
    ORDER BY buffer_gets DESC
) WHERE rownum <= 30;

2. 執行頻率最高

SELECT sql_text, executions FROM (
    SELECT sql_text, executions
    FROM v$sqlarea
    ORDER BY executions DESC
) WHERE rownum < 81;

3. 物理讀取最多 (Disk Reads)

SELECT sql_text, disk_reads FROM (
    SELECT sql_text, disk_reads
    FROM v$sqlarea
    ORDER BY disk_reads DESC
) WHERE rownum < 21;

4. 排序操作最多

SELECT sql_text, sorts FROM (
    SELECT sql_text, sorts
    FROM v$sqlarea
    ORDER BY sorts DESC
) WHERE rownum < 21;

潛在綁定變數問題偵測

解析次數多但執行次數少，建議改用綁定變數：

SET PAGESIZE 600 LINESIZE 120
SELECT SUBSTR(sql_text,1,80) "SQL", COUNT(*), SUM(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY SUBSTR(sql_text,1,80)
HAVING COUNT(*) > 30
ORDER BY 2;

游標使用狀況監控

-- 目前開啟游標總數
SELECT SUM(a.value), b.name
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic#
  AND b.name = 'opened cursors current'
GROUP BY b.name;

-- 依用戶與 SQL 統計開啟游標數
SELECT user_name, sql_text, COUNT(0)
FROM v$open_cursor
GROUP BY user_name, sql_text
HAVING COUNT(0) > 30;

特定用戶當前執行 SQL 查詢

SELECT sql_text
FROM v$sqltext_with_newlines
WHERE (hash_value, address) IN (
    SELECT sql_hash_value, sql_address
    FROM v$session
    WHERE username = '&username'
)
ORDER BY address, piece;

後續優化建議

• 對高資源 SQL 執行 EXPLAIN PLAN 分析執行計畫。
• 檢查索引缺失、統計資料過期、不當連接順序。
• 考慮 SQL Profile、SQL Plan Baseline 固定優良計畫。
• 應用端改用綁定變數、減少硬解析。