Oracle CPU 性能監控 SQL 大全：解析高解析、Session 佔用、Top 10 會話

1. 高解析 SQL 識別

SELECT sql_text, parse_calls, executions
FROM v$sqlarea
ORDER BY parse_calls DESC;

2. 系統級解析統計

SELECT name, value
FROM v$sysstat
WHERE name LIKE 'parse count%';

3. 作業系統 CPU 狀態

SELECT * FROM v$osstat;

4. Session 級 CPU 使用排名

SELECT ss.sid, se.command, ss.value AS cpu_usage,
       se.username, se.program
FROM v$sesstat ss, v$session se
WHERE ss.statistic# IN (SELECT statistic# FROM v$statname WHERE name = 'CPU used by this session')
  AND se.sid = ss.sid
  AND ss.sid > 6  -- 過濾後台進程
ORDER BY cpu_usage DESC;

5. 指定 Session 等待事件與 SQL 關聯

SELECT s.sid, s.event, s.machine, s.osuser, s.wait_time,
       w.seq#, q.sql_text
FROM v$session_wait w, v$session s, v$process p, v$sqlarea q
WHERE s.paddr = p.addr
  AND s.sid = :target_sid
  AND s.sql_address = q.address;

6. Top 10 高 CPU Session 及其 SQL 全景

SELECT s.sid, w.wait_time, w.seconds_in_wait, w.state,
       w.wait_time_micro, w.time_remaining_micro, w.time_since_last_wait_micro,
       p.username, s.status, s.server, s.schemaname, s.osuser, s.machine,
       p.terminal, p.program, s.logon_time, w.event, w.wait_class,
       s.tracefile, q.sql_text, q.last_active_time
FROM v$session_wait w, v$session s, v$process p, v$sqlarea q
WHERE s.paddr = p.addr
  AND s.sid IN (
    SELECT sid FROM (
      SELECT ss.sid
      FROM v$sesstat ss, v$session se
      WHERE ss.statistic# IN (SELECT statistic# FROM v$statname WHERE name = 'CPU used by this session')
        AND se.sid = ss.sid AND ss.sid > 6
      ORDER BY ss.value DESC
    ) WHERE ROWNUM < 11
  )
  AND s.sql_address = q.address;

此查詢可快速定位佔用 CPU 最多的用戶會話、等待事件、執行 SQL，是性能應急排查利器。