Oracle 高 CPU 消耗診斷實戰：vmstat 定位、SQL 追蹤、執行計劃分析與綁定變量優化

案例環境

• OS: Solaris 8
• Oracle: 8.1.7.4
• 現象：開發反饋系統極慢，業務受阻。

第一步：OS 級定位

vmstat 3
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s6 s9 s1 sd in sy cs us sy id
131 0 0 ... 97 3 0   <-- us (user CPU) 長期 96-97%，r (run queue) 130+ 進程

結論：CPU 資源耗盡，大量任務排隊等待 CPU。

第二步：鎖定消耗 CPU 的會話

• ps -ef | grep oracle 找到 Oracle 影子進程 PID。
• top 或 prstat 確認哪個 PID 佔用 CPU 最高。
• 映射 PID 到 Oracle Session（v$process.addr = v$session.paddr）。

第三步：獲取問題 SQL

• 對該 Session 開啟 10046 事件追蹤（ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'）。
• 或查詢 v$sql 找 buffer_gets、cpu_time 高的語句。

第四步：分析執行計劃與優化

1. EXPLAIN PLAN 觀察全表掃描、缺失索引、笛卡爾積等。
2. 檢查統計信息是否過期（DBMS_STATS.GATHER_TABLE_STATS）。
3. 重寫 SQL：使用綁定變量減少硬解析、加提示（/*+ INDEX(...) */）、調整連接順序。
4. 必要時建立/調整索引、分區表、物化視圖。

核心方法論

1. OS 工具（vmstat/top）確認 CPU 瓶頸。
2. 進程級定位到具體 Oracle Session。
3. SQL 追蹤/動態視圖鎖定高消耗 SQL。
4. 執行計劃 + 統計信息 + 綁定變量 三板斧優化。
5. 驗證優化效果，建立變更管理流程。