Oracle 惡劣 SQL 發現與分析實戰：v$sqlarea 多維度掃描與定期巡檢腳本

Oracle 共享池 (`v$sqlarea`) 保留最近執行的 SQL 文本與累積統計。透過多維度掃描，可快速定位**高耗資源、高頻執行、高 I/O、高排序**的惡劣 SQL，納入定期巡檢體系。

核心視圖：`v$sqlarea` 關鍵欄位

四大掃描維度標準查詢

1. 高邏輯讀 (CPU/緩衝區壓力)

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

**閾值建議**：依系統基線調整 (如前 1% 或 > 總邏輯讀 5%)。

2. 高執行次數 (熱點 SQL、游標共用機會)

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

**關注**：`EXECUTIONS` 極高但 `BUFFER_GETS/EXECUTIONS` 低 → 適合快取/結果集快取；`PARSE_CALLS ≈ EXECUTIONS` → **未使用繫結變數**，需改寫為參數化 SQL。

3. 高物理讀 (I/O 熱點)

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

4. 高排序 (PGA/臨時表空間壓力)

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

進階分析：硬解析風暴偵測 (未使用繫結變數)

SET PAGESIZE 600;
SET 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;

**邏輯**：相似文本 (前 80 字元) 出現 >30 個不同游標，但每個執行 <5 次 → **字面值 SQL 暴增游標**，消耗共享池、增加硬解析 CPU。**對策**：強制 `CURSOR_SHARING=FORCE` (謹慎)、應用端改用繫結變數、SQL Profile 固定計畫。

游標洩漏偵測

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

-- 使用者/SQL 維度開啟游標數 (超過閾值如 30)
SELECT user_name, sql_text, COUNT(0)
FROM v$open_cursor
GROUP BY user_name, sql_text
HAVING COUNT(0) > 30;

-- 系統層級游標統計
SET PAGES 300;
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;

即時 Session SQL 追蹤

-- 指定使用者正在執行的 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;

定期巡檢自動化建議

1. **排程**：每日/每週低峰期 (如 02:00) 執行掃描腳本。
2. **輸出**：HTML/CSV 報表郵件寄送 DBA 群組。
3. **基線化**：記錄歷史 Top N 統計，偵測趨勢變化 (如某 SQL `BUFFER_GETS` 突然暴增 3 倍)。
4. **整合 AWR/ASH** (Enterprise + Diagnostic Pack)：`DBA_HIST_SQLSTAT`、`DBA_HIST_ACTIVE_SESS_HISTORY` 進行更深度時間序列分析。
5. **追蹤修復閉環**：掃描發現 → 建立調優工單 → 驗證修復 → 關單歸檔。

⚠️ 執行風險提醒

掃描 `v$sqlarea` 需讀取共享池鎖 (latch)，**頻繁/大範圍掃描會增加系統負載**。**嚴禁**在高峰期高頻執行；**務必**在低峰期、加 `WHERE` 過濾 (如 `BUFFER_GETS > 閾值`)、限制 `ROWNUM`、避免 `SELECT *` 全表掃描共享池。

1. 上一篇文章： SQL Server 系統資料庫深度解析：master、model、msdb、tempdb、distribution 運作原理與維護要點
2. 下一篇文章： Oracle 即時追蹤前端發出的 SQL：v$open_cursor 與 v$session 實戰查詢

關於SqlServer

Pro*C/C++ 入門筆記：VC++ 6.0 環境配置與第一個範例

Pro*C/C++ 是 Oracle 提供的嵌入式 SQL

SQL Server sys.dm_os_waiting_tasks 實戰解析（中）：並行等待爆炸成因與 Task/Worker/Scheduler 模型詳解

承接上篇發現 sys.dm_exec_requests 在並

SQL Server 2005：XML、T-SQL 與 CLR 整合打造全新資料庫開發平台

本文基於 Microsoft SQL Server 2005

MySQL 5.6 整合 memcached 實現 NoSQL 存取：降低查詢延遲的新嘗試

Oracle 在 2011 MySQL User Confe