Oracle SQL 審計利器：V$SESSION/V$SQL/V$SQLAREA 實時/歷史/Top SQL 全維度查詢

「線上 SQL 什麼時候跑的？跑多久？誰跑的？執行計劃變了嗎？」本文提供**實時會話 SQL、歷史執行記錄、高消耗 Top SQL、等待事件關聯**四大維度查詢腳本，直擊生產性能現場。

一、正在執行的 SQL（實時會話視角）

SELECT s.username,
       s.sid,
       s.serial#,
       s.status,
       s.machine,
       s.program,
       s.module,
       s.sql_id,
       sq.sql_text,
       sq.sql_fulltext
FROM v$session s
JOIN v$sql sq ON s.sql_id = sq.sql_id AND s.sql_child_number = sq.child_number
WHERE s.status = 'ACTIVE'
  AND s.type != 'BACKGROUND'
  AND s.username IS NOT NULL
ORDER BY s.last_call_et DESC;

• 關鍵欄位：SQL_ID + CHILD_NUMBER 唯一標識游標，關聯 V$SQL_PLAN 獲取實時執行計劃。
• LAST_CALL_ET：當前操作已耗秒數，> 60 秒關注長跑 SQL。

二、歷史執行記錄（V$SQLAREA + 首次加載時間）

SELECT sql_id,
       sql_text,
       first_load_time,
       last_load_time,
       executions,
       elapsed_time/1e6 AS elapsed_sec,
       cpu_time/1e6 AS cpu_sec,
       buffer_gets,
       disk_reads,
       rows_processed
FROM v$sqlarea
WHERE first_load_time BETWEEN TO_DATE('2024-01-15 09:00','YYYY-MM-DD HH24:MI')
                          AND TO_DATE('2024-01-15 10:00','YYYY-MM-DD HH24:MI')
ORDER BY elapsed_time DESC;

• FIRST_LOAD_TIME 精度到秒，適合事後追溯某時間窗口的 SQL。
• SQL_FULLTEXT 包含完整 SQL 文本（CLOB），長文本需 DBMS_LOB.SUBSTR 截取。

三、綜合會話+SQL+CPU 排序（找出佔用 CPU 最高的會話）

SELECT s.osuser,
       s.program,
       s.username,
       s.schemaname,
       s.status,
       sq.cpu_time/1e6 AS cpu_sec,
       sq.elapsed_time/1e6 AS ela_sec,
       sq.buffer_gets,
       sq.disk_reads,
       sq.sql_text
FROM v$session s
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id AND s.sql_child_number = sq.child_number
WHERE s.type != 'BACKGROUND'
ORDER BY sq.cpu_time DESC NULLS LAST;

四、長文本 SQL 分片查看（V$SQLTEXT）

SELECT sql_id, piece, sql_text
FROM v$sqltext
WHERE sql_id = 'abcdef1234567890'
ORDER BY piece;

五、Top 10 物理讀最高 SQL（I/O 壓力源）

SELECT *
FROM (
  SELECT parsing_user_id,
         executions,
         sorts,
         command_type,
         disk_reads,
         buffer_gets,
         sql_text
  FROM v$sqlarea
  ORDER BY disk_reads DESC
)
WHERE ROWNUM <= 10;

六、高 I/O 等待會話關聯（V$SESSION_WAIT + V$SESS_IO）

SELECT se.sid,
       se.serial#,
       p.spid AS os_pid,
       se.username,
       se.status,
       se.machine,
       se.program,
       se.module,
       sw.event,
       sw.p1text, sw.p1,
       si.physical_reads,
       si.block_changes
FROM v$session se
JOIN v$session_wait sw ON se.sid = sw.sid
JOIN v$sess_io si ON se.sid = si.sid
JOIN v$process p ON se.paddr = p.addr
WHERE se.sid > 6
  AND sw.wait_time = 0
  AND sw.event NOT LIKE '%SQL*Net%'
  AND sw.event NOT LIKE '%rdbms ipc%'
ORDER BY si.physical_reads DESC;

• 過濾掉網絡/IPC 空等待，聚焦 db file scattered read、direct path read、log file sync 等實質 I/O 事件。
• PHYSICAL_READS 累計物理讀塊數，BLOCK_CHANGES 修改塊數，識別「讀多寫少」或「寫多讀少」會話。

七、生產級 SQL 審計儀表盤建議

八、AWR/ASH 歷史深度分析（需 Diagnostic Pack）

-- AWR 快照間隔 Top SQL
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
  l_dbid => 1234567890,
  l_inst_num => 1,
  l_bid => 1001,
  l_eid => 1002
));

-- ASH 最近 10 分鐘等待事件分布
SELECT event, COUNT(*) AS cnt, ROUND(COUNT(*)*100/SUM(COUNT(*)) OVER(),2) AS pct
FROM v$active_session_history
WHERE sample_time >= SYSDATE - 10/1440
GROUP BY event
ORDER BY cnt DESC;

將上述腳本封裝為 Python/Go 守護進程，寫入 Prometheus + Grafana，構建「實時 SQL 審計 → 歷史趨勢 → 自動優化建議」閉環，讓性能問題「無處遁形」。

1. 上一篇文章： Oracle 後台進程全圖鑑：核心/可選/RAC/工具/其他 5 大類 30+ 進程職責詳解
2. 下一篇文章： Oracle 存儲過程權限模型深度解析：定義者權限 vs 調用者權限、Role 生效邊界、動態 SQL 繞過編譯期檢查

Oracle 資料庫相關知識

Oracle 11g 從 11.2.0.1 升級至 11.2.0.4 完整實戰教學 (Linux 圖形介面安裝)

升級前確認 SELECT * FROM v$version

Oracle 資料庫升級與遷移方法論：升級路徑、測試計畫、手工腳本與 DBUA 實戰

一、升級必要性評估 需使用新版特性（In-Memory、M

Windows 系統安裝 Oracle 11g 資料庫圖文完整教學

一、下載安裝檔 官方下載頁：Oracle Database

Oracle 9i 全文檢索技術架構深度解析

Oracle Text 架構概覽 圖 1 Oracle Te