您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle CPU 100% 疑難排查實戰：從 vmstat/top 定位問題 SQL 到根因解決

編輯：Oracle 資料庫相關知識

案例背景

平台：Solaris 8 + Oracle 8.1.7.4
症狀：應用回應極慢，業務幾乎癱瘓
工具：vmstat, top, ps, Oracle 動態視圖

排查步驟實錄

Step 1：OS 層面確認 CPU 壓力來源

$ vmstat 5
kthr      memory            page            disk          faults      cpu
 r b w   swap  free  re  mf pi po fr de sr s0 s1 s2 s3  in  sy  cs us sy id
 45 0 0 1200M 50M  0  0  0  0  0  0  0  0  0  0  0  500 2000 3000 95 5 0
-- r (run queue) = 45，遠超 CPU 核心數，系統嚴重 CPU-bound

Step 2：識別佔用 CPU 的進程

$ top
PID USERNAME PRI NICE SIZE RES STATE TIME CPU COMMAND
12345 oracle   58  0  200M 180M cpu  12:34 12% oracleSID (PMON)
12346 oracle   58  0  200M 180M cpu  11:56 11% oracleSID (DBW0)
...
-- 無單一進程佔用極高 CPU (如 90%+)，但 **Oracle 進程總數約 300 個** (正常 ~100)

Step 3：分析進程暴增根因

正常連接數 ~100，現為 300+，大量 **Dedicated Server Processes** 佔用 CPU。
推測：應用連接池洩漏、或未使用連接池頻繁建立/銷毀連接、或邏輯備份/排程同時跑滿連接。

Step 4：鎖定高耗 CPU SQL (核心關鍵)

-- 1. 找出當前高 CPU 會話
SELECT s.sid, s.serial#, s.username, s.machine, s.program, s.sql_id, 
       t.value/100 cpu_sec
FROM v$session s, v$sesstat t, v$statname n
WHERE s.sid = t.sid
  AND t.statistic# = n.statistic#
  AND n.name = 'CPU used by this session'
  AND t.value > 10000  -- 超過 10 秒 CPU
ORDER BY t.value DESC;

-- 2. 取得 SQL 文本與執行計畫
SELECT sql_text, sql_fulltext FROM v$sql WHERE sql_id = '';

-- 3. 檢查執行計畫是否最佳
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('', NULL, 'ALLSTATS LAST'));

Step 5：典型高 CPU SQL 模式與對策

模式	特徵	對策
全表掃描大表	`TABLE ACCESS FULL`、Rows 淨、Cost 高	建立索引、分區裁剪、改寫 SQL
笛卡兒積 / 缺少 Join 條件	`MERGE JOIN CARTESIAN`	補全 Join 條件、檢查統計資訊
硬解析風暴	`V$SQLAREA` 大量相似 SQL (Literal 不同)	強制綁定變數、`CURSOR_SHARING=FORCE/SIMILAR`
PL/SQL 迴圈內逐筆 SQL	大量 `EXECUTE IMMEDIATE` 或隱性游標	改用 `BULK COLLECT / FORALL`
遞迴/階層查詢失控	`CONNECT BY` 無過濾條件	加入 `WHERE LEVEL <= N`、物化路徑

Step 6：連接數暴增根因修正

應用層：檢查連接池配置 (min/max/idle timeout)、確保 finally 區塊關閉連接。
資料庫層：啟用 SHARED SERVERS (MTS) 或 DRCP (11g+) 減少進程數。
監控告警：設定 PROCESSES 參數上限、V$RESOURCE_LIMIT 監控。

預防性監控儀表板

-- 每 5 分鐘收集一次關鍵指標
INSERT INTO perf_snap (snap_time, cpu_pct, sess_cnt, active_sess, top_sql_id)
SELECT SYSDATE,
       (SELECT value FROM v$sysstat WHERE name='CPU used by this session') / 100,
       (SELECT COUNT(*) FROM v$session),
       (SELECT COUNT(*) FROM v$session WHERE status='ACTIVE'),
       (SELECT sql_id FROM (
         SELECT sql_id, cpu_time FROM v$sqlarea ORDER BY cpu_time DESC
       ) WHERE ROWNUM=1)
FROM dual;