Oracle 高 CPU 進程精準定位與 SQL 追蹤全流程

當 Oracle 進程佔用 CPU 居高不下，需從 OS 進程層層穿透至具體 SQL。標準四步走：

Step 1：OS 層定位高 CPU 進程 (SPID)

top -c -p $(pgrep -d',' -f ora_.*_ORCL)
# 或
ps -eo pid,user,%cpu,comm --sort=-%cpu | head -20

記錄 PID (即 Oracle SPID)，假設為 3575。

Step 2：資料庫層查詢對應 Session

SELECT s.sid, s.serial#, s.username, s.machine, s.program, s.osuser,
       s.logon_time, s.status, p.spid
FROM   v$session s, v$process p
WHERE  p.addr = s.paddr
AND    p.spid = 3575;  -- Step 1 取得的 SPID

取得 SID、SERIAL#。

Step 3：取得正在執行的 SQL

-- 當前 SQL
SELECT sql_text
FROM   v$sqltext_with_newlines
WHERE  hash_value = (SELECT sql_hash_value FROM v$session WHERE sid = :sid)
ORDER BY piece;

-- 或最近一次 SQL
SELECT sql_text
FROM   v$sqltext_with_newlines
WHERE  hash_value = (SELECT prev_hash_value FROM v$session WHERE sid = :sid)
ORDER BY piece;

Step 4：完整診斷腳本 (一次取齊)

SELECT a.username, a.machine, a.program, a.sid, a.serial#, a.status,
       c.piece, c.sql_text
FROM   v$session a, v$process b, v$sqltext c
WHERE  b.spid = '&spid'
AND    b.addr = a.paddr
AND    a.sql_address = c.address(+)
ORDER BY c.piece;

進階：鎖等待與執行計劃

-- 該 Session 等待的鎖
SELECT * FROM v$lock
WHERE  kaddr IN (SELECT lockwait FROM v$session WHERE sid = :sid);

-- SQL 完整執行計劃
EXPLAIN PLAN FOR <取得的 SQL>;
SELECT * FROM TABLE(dbms_xplan.display);

終極大招：SQL Trace + TKPROF (全量分析)

-- 1. 開啟 Session 級 Trace
EXEC dbms_system.set_sql_trace_in_session(:sid, :serial#, TRUE);

-- 2. 讓問題 SQL 跑完

-- 3. 關閉 Trace
EXEC dbms_system.set_sql_trace_in_session(:sid, :serial#, FALSE);

-- 4. 服務器端 user_dump_dest 找到 *.trc，轉換
tkprof ora_12345.trc output.txt explain=user/pass sys=no

處理建議

• 若為無索引全表掃描 → 補索引或改寫 SQL。
• 若為惡意迴圈/死遞迴 → Kill Session 並通知開發修正。
• 若為統計資訊過期 → DBMS_STATS.GATHER_TABLE_STATS。

從 SPID 到 SQL Text 僅需 3 條查詢，建議收藏成腳本庫，關鍵時刻秒級定位。