Oracle 查詢語句審計：利用 V$SQLAREA 追蹤使用者執行歷史

前言

共享池中的 V$SQLAREA 保留最近執行過的 SQL 文本與統計資訊，是事後審計、效能分析、異常 SQL 追蹤的首選視圖。本文提供實用查詢範本。

核心查詢範本

1. 查詢特定使用者最近執行的 SQL (按最後執行時間倒序)

SELECT sql_id, sql_text, executions, elapsed_time/1e6 AS elapsed_sec,
       cpu_time/1e6 AS cpu_sec, buffer_gets, disk_reads, last_active_time
FROM   v$sqlarea
WHERE  parsing_schema_name = UPPER('APP_USER')
ORDER BY last_active_time DESC;

2. 全庫最近活躍 SQL (不限使用者)

SELECT sql_id, parsing_schema_name, sql_text, executions,
       ROUND(elapsed_time/1e6, 3) AS elapsed_sec,
       last_active_time
FROM   v$sqlarea
ORDER BY last_active_time DESC;

3. 關鍵欄位說明

進階：找出高消耗 SQL

-- 總耗時最高 Top 10
SELECT * FROM (
  SELECT sql_id, sql_text, elapsed_time/1e6 AS total_sec, executions,
         ROUND(elapsed_time/NULLIF(executions,0)/1e6, 4) AS avg_sec
  FROM   v$sqlarea
  WHERE  parsing_schema_name != 'SYS'
  ORDER BY elapsed_time DESC
) WHERE ROWNUM <= 10;

-- 平均耗時最高 (可能為未優化的報表查詢)
SELECT * FROM (
  SELECT sql_id, sql_text, ROUND(elapsed_time/NULLIF(executions,0)/1e6, 4) AS avg_sec, executions
  FROM   v$sqlarea
  WHERE  executions > 10
  ORDER BY avg_sec DESC
) WHERE ROWNUM <= 10;

權限需求與注意事項

• 查詢 V$SQLAREA 需 **SELECT_CATALOG_ROLE** 或 **DBA** 權限 (或具備 SELECT ANY DICTIONARY 系統權限)。
• 共享池有限，舊 SQL 會被老化淘汰，**無法保留完整歷史**；長期審計請啟用 **AWR (Automatic Workload Repository)** 或 **SQL Plan Baseline** 捕捉。
• V$SQLAREA 為 **Library Cache** 層面統計，**不包含** 未共用游標 (如字面值不同未啟用 Cursor Sharing) 的每次執行細節。

相關實用視圖