善用 Oracle 動態效能視圖採集 SQL 調優數據：V$SQL、V$SQLAREA、V$SQLSTATS 實戰指南

四大視圖對比

權限授予

非 DBA 開發人員需：

GRANT SELECT_CATALOG_ROLE TO dev_user;

實戰查詢範例

1. 找出高 CPU/高 I/O SQL (V$SQLAREA)

SELECT sql_id, executions, buffer_gets, disk_reads,
       ROUND(elapsed_time/1e6/NULLIF(executions,0),3) avg_elapsed_sec,
       sql_text
FROM   v$sqlarea
WHERE  executions > 0
ORDER  BY buffer_gets DESC
FETCH FIRST 20 ROWS ONLY;

2. 追蹤特定 SQL 歷史效能 (V$SQLSTATS)

SELECT sql_id, plan_hash_value, executions,
       ROUND(elapsed_time/1e6/NULLIF(executions,0),3) avg_elapsed_sec,
       last_active_time
FROM   v$sqlstats
WHERE  sql_id = 'abc123def456'
ORDER  BY last_active_time;

3. 還原完整 SQL 文本 (V$SQLTEXT)

SELECT sql_text
FROM   v$sqltext_with_newlines
WHERE  sql_id = 'abc123def456'
ORDER  BY piece;

進階技巧

• 結合 V$SQL_PLAN / DBMS_XPLAN.DISPLAY_CURSOR 看實際執行計劃。
• 用 SQL_ID + CHILD_NUMBER 精確定位 Child Cursor。
• 定期快照 V$SQLSTATS 到歷史表，構建 SQL 效能基線庫。

避坑提醒

• V$SQL/V$SQLAREA 受共用池淘汰機制影響，長時間未執行的 SQL 會消失。
• V$SQLSTATS 保留較久但非永久，關鍵 SQL 必須自行歸檔。
• 高併發系統查詢這些視圖本身會增加 Library Cache 閂鎖壓力，建議低峰期或用 AWR/ASH 替代。