Oracle會話SQL監控：V$SESSION與V$SQL聯查實戰查詢

核心動態視圖關係

• V$SESSION：當前會話信息，含SQL_ADDRESS、SQL_HASH_VALUE、PADDR(進程地址)
• V$SQLAREA/V$SQL：共享池SQL統計，主鍵ADDRESS、HASH_VALUE
• V$PROCESS：後台/前台進程，主鍵ADDR
• V$OPEN_CURSOR：會話打開的游標，含SQL_TEXT

五大實戰查詢場景

1. 查看會話正在執行的SQL

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine,
       sa.sql_text, sa.sql_id
FROM v$session s
JOIN v$sqlarea sa ON s.sql_address = sa.address
WHERE s.status = 'ACTIVE' AND s.username IS NOT NULL;

2. 查詢前台用戶打開的游標SQL

SELECT oc.sid, oc.user_name, oc.sql_text
FROM v$open_cursor oc
WHERE oc.sid IN (
    SELECT sid FROM v$session 
    WHERE status = 'ACTIVE' AND username IS NOT NULL
);

3. 根據OS進程ID(SPID)反查會話

SELECT s.* FROM v$session s
WHERE s.paddr IN (
    SELECT addr FROM v$process WHERE spid = &目標SPID
);

4. 根據SID查詢對應進程信息

SELECT p.* FROM v$process p
WHERE p.addr IN (
    SELECT paddr FROM v$session WHERE sid = &目標SID
);

5. DBA審計特定用戶正在執行的操作

SELECT sql.sql_text
FROM v$sql sql
JOIN v$session ses ON sql.address = ses.sql_address
WHERE ses.sid = &目標SID;

進階監控技巧

長時間運行SQL監控 (11g+)

SELECT sql_id, sql_text, elapsed_time/1e6 elapsed_sec, 
       cpu_time/1e6 cpu_sec, buffer_gets, disk_reads
FROM v$sql_monitor
WHERE status = 'EXECUTING' AND elapsed_time > 60000000;  -- >60秒

會話級等待事件分析

SELECT s.sid, s.event, s.p1text, s.p1, s.p2text, s.p2, s.wait_time, s.seconds_in_wait
FROM v$session_wait s
WHERE s.sid = &目標SID;

實時SQL執行計劃

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
    (SELECT sql_id FROM v$session WHERE sid = &SID),
    (SELECT sql_child_number FROM v$session WHERE sid = &SID),
    'ALLSTATS LAST'));

權限與工具建議

• 開發賬號授予SELECT_CATALOG_ROLE
• 生產環境建議使用Enterprise Manager或SQL Developer DBA面板圖形化監控
• 關鍵業務時段設置ALTER SYSTEM SET SQL_TRACE=TRUE配合TKPROF深度分析
• 定期導出V$SQLSTATS構建性能基線庫