Oracle CPU 100%診斷實戰：從vmstat到SQL調優的完整排查鏈路

故障現象與初步定位

• 業務反饋系統緩慢，CPU資源耗盡
• OS層面：vmstat顯示r(運行隊列)持續>100，us(用戶態CPU) 95%+
• 進程層面：top顯示大量Oracle進程各佔1%左右，無單一佔用高進程
• 數據庫層面：會話數~300 (正常~100)，大量db file scattered/sequential read等待

分層排查鏈路

第一層：OS資源視角

# 1. vmstat 3 觀察持續狀態
# 關鍵列: r(運行隊列), us/sy/id(CPU分佈), cs(上下文切換)

# 2. top -H -p  觀察線程級CPU
# 3. ps -ef | grep ora | wc -l 統計進程數
# 4. sar -u 1 10 歷史CPU趨勢

第二層：數據庫會話視角

-- 1. 活躍會話等待事件
SELECT sid, event, p1text, p1, p2text, p2, wait_time, seconds_in_wait
FROM v$session_wait
WHERE wait_time = 0 AND event NOT IN ('SQL*Net message from client', 'pmon timer', 'rdbms ipc message');

-- 2. 會話級SQL關聯
SELECT s.sid, s.username, s.sql_id, s.sql_child_number, 
       sq.sql_text, sq.executions, sq.buffer_gets, sq.cpu_time/1e6 cpu_sec
FROM v$session s
JOIN v$sql sq ON s.sql_id = sq.sql_id AND s.sql_child_number = sq.child_number
WHERE s.status = 'ACTIVE' AND s.type = 'USER';

第三層：SQL執行計劃視角

-- 1. 實時執行計劃
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
    'sql_id', child_number, 'ALLSTATS LAST'));

-- 2. 歷史執行統計
SELECT plan_hash_value, executions, 
       elapsed_time/1e6/NULLIF(executions,0) avg_elapsed_sec,
       cpu_time/1e6/NULLIF(executions,0) avg_cpu_sec,
       buffer_gets/NULLIF(executions,0) avg_lio
FROM v$sql
WHERE sql_id = '目標SQL_ID'
ORDER BY last_active_time DESC;

本案例根因分析

現象

• 大量會話等待db file scattered read(全表掃描)和db file sequential read(索引讀取)
• 會話數異常增長(300 vs 100正常) → 連接池洩漏或應用邏輯死循環
• 無單一高CPU SQL，但大量中等消耗SQL並發執行

根因

1. 應用連接池配置錯誤：最大連接數過大、無空閒超時回收、事務未提交導致連接不釋放
2. 缺少關鍵索引：高頻查詢走全表掃描，產生大量db file scattered read
3. 統計信息過期：優化器誤選全表掃描而非索引
4. 未使用綁定變量：大量硬解析消耗CPU，共享池碎片化

解決方案實施

即時緩解

-- 1. 終止長時間空閒會話
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 2. 刷新共享池 (謹慎，會導致硬解析風暴)
ALTER SYSTEM FLUSH SHARED_POOL;

-- 3. 臨時調整優化器模式
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;

根治措施

• 修正連接池：最大連接數=CPU核心數×2-4，設置空閒超時、驗證查詢
• 補充缺失索引：基于V$SQL_PLAN和DBA_ADVISOR建議
• 建立統計信息收集策略：DBMS_STATS.GATHER_SCHEMA_STATS定時任務
• 強制綁定變量：CURSOR_SHARING = FORCE (評估影響後開啟)
• 應用碼審查：修復連接洩漏、優化N+1查詢模式

預防性監控體系

核心工具箱

• AWR/ASH報告：問題時間窗口的全景快照
• SQL Tuning Advisor：自動索引/Profile/重寫建議
• Real-time SQL Monitoring：長時間SQL逐步執行監控
• DBMS_XPLAN：執行計劃深度分析
• OS Watcher：系統級資源持續記錄