您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

Oracle 惡劣 SQL 發掘實戰：從 V$SQLAREA 定位高消耗、高頻率、高排序與綁定變數缺失問題

編輯：關於SqlServer

前言

Oracle 資料庫效能調優的第一步是「找出惡劣 SQL」。V$SQLAREA 共用游標區提供了豐富的執行統計資訊。本文提供一組標準化查詢腳本，涵蓋緩衝區獲取、執行次數、磁碟讀取、排序操作、綁定變數缺失、游標洩漏等六大維度，並說明執行時機與風險控制。

執行原則與風險控制

低峰期執行：建議在業務低峰（如凌晨）排程執行，避免對系統表群組操作造成額外負載
定期巡檢：建議月頻執行，建立基線對比
結果審閱：查詢結果需結合應用場景人工判斷，不可盲目據此建立索引或改寫

六大維度發掘腳本

1. 高緩衝區獲取

邏輯讀過高通常意味著全表掃描、索引效率低或連接順序不佳。

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

2. 高執行頻率

極高執行次數可能為「逐列處理」模式（如迴圈單筆 INSERT/UPDATE），適合批次化改寫。

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

3. 高磁碟讀取

物理讀過高反映資料未在緩衝區、索引缺失或統計資料過期。

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

4. 高排序操作

大量排序消耗 PGA 記憶體與 TEMP 表空間，可能需調整 SORT_AREA_SIZE、PGA_AGGREGATE_TARGET 或改寫避免排序。

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

5. 解析過多、執行極少：綁定變數缺失

相似 SQL 因字面值不同產生大量子游標，導致共用池碎片化與解析風暴。

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

對策：改用綁定變數、設定 CURSOR_SHARING = FORCE/SIMILAR（謹慎）、啟用 SESSION_CACHED_CURSORS。

6. 開啟游標監控

游標未關閉導致 ORA-01000: maximum open cursors exceeded。

-- 總開啟游標數
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;

-- 當前開啟游標計數
SELECT COUNT(0) FROM v$open_cursor;

-- 高頻游標 SQL（同一用戶同一 SQL 開啟 >30 個）
SELECT user_name, sql_text, COUNT(0)
FROM v$open_cursor
GROUP BY user_name, sql_text
HAVING COUNT(0) > 30;

7. 即時追蹤特定用戶正在執行的 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 後，進一步分析：

-- 取得 SQL_ID 對應的執行計畫
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id_here', NULL, 'ALL'));

-- 關聯等待事件
SELECT s.sid, s.sql_id, s.event, s.seconds_in_wait
FROM v$session s
WHERE s.sql_id = 'sql_id_here';