Oracle CPU 使用率監控與高消耗 SQL 定位實戰查詢

1. 找出高解析 (PARSE) 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 狀態 (10g+)

SELECT * FROM v$osstat;
-- 關鍵欄位：NUM_CPUS, IDLE_TIME, BUSY_TIME, USER_TIME, SYS_TIME

4. Session 級 CPU 消耗排名

SELECT ss.sid, se.command, ss.value AS cpu_centiseconds,
       se.username, se.program
FROM   v$sesstat ss, v$session se
WHERE  ss.statistic# = (SELECT statistic# FROM v$statname WHERE name = 'CPU used by this session')
AND    se.sid = ss.sid
AND    ss.sid > 6  -- 排除背景進程
ORDER  BY cpu_centiseconds DESC;

5. 針對高 CPU 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 完整資訊

SELECT s.sid, w.wait_time, w.seconds_in_wait, w.state,
       w.wait_time_micro, w.time_remaining_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# = (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;

後續分析建議

1. 取得高 CPU SQL_TEXT → EXPLAIN PLAN / DBMS_XPLAN.DISPLAY_CURSOR 分析執行計畫。
2. 檢查 PARSE CALLS 高 → 應用端改用綁定變數、啟用游標快取。
3. 檢查等待事件：db file scattered read (全表掃描)、log file sync (Commit 過頻) 等。
4. 結合 V$SQL_PLAN_STATISTICS_ALL 取得實際執行統計 (A-Rows, A-Time)。