某生產環境 Oracle 8.1.7.4 on Solaris 8 系統回報嚴重效能下降，業務系統近乎癱瘓。本文記錄完整診斷流程，展示如何從作業系統層面切入，快速鎖定消耗 CPU 的問題 SQL。
bash-2.03$ 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 5368072 1518360 56 691 0 2 2 0 0 0 1 0 0 3011 7918 2795 97 3 0
131 0 0 5377328 1522464 81 719 0 2 2 0 0 0 1 0 0 2766 8019 2577 96 4 0
...關鍵指標解讀：
r (run queue) 持續維持 125~136，遠超 CPU 核心數，表示大量程序搶佔 CPU；us (user CPU) 高達 95~97%，sy (system CPU) 3~5%，id (idle) 接近 0，確認 CPU 瓶頸在使用者空間程式；pi/po (page in/out) 為 0，排除記憶體交換導致的 I/O 等待。$ top
last pid: 28313; load averages: 99.90, 117.54, 125.71
296 processes: 186 sleeping, 99 running, 2 zombie, 9 on cpu
CPU states: 0.0% idle, 96.5% user, 3.5% kernel, 0.0% iowait
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
...雖 top 未顯示單一進程佔用極高 CPU，但 99 個 running 進程與極高負載平均值（125+）暗示：大量 Oracle 前景程序（影子程序）同時活躍，各自佔用少量 CPU，總和導致系統過載。
查詢 V$SESSION_WAIT、V$SESSTAT 結合 V$SQL，找出 CPU used by this session 遞增最快的 Session。
SELECT sql_text, executions, buffer_gets, disk_reads, cpu_time
FROM v$sql
WHERE cpu_time > 1000000 -- 微秒
ORDER BY cpu_time DESC;對目標 SQL 執行 EXPLAIN PLAN，重點檢查：
| 根因類別 | 典型徵兆 | 解決方向 |
|---|---|---|
| 缺乏適當索引 | 大表全掃、高 buffer_gets | 建立複合索引、收集統計資訊 |
| SQL 寫法導致最佳化器誤判 | 執行計畫頻繁切換、Cardinality 偏差大 | 改寫 SQL、使用 Hint、綁定變數窺探 |
| 不當的 PL/SQL 迴圈/遊標 | 大量簡單 SQL 重複執行、高 executions | 改為集合操作（BULK COLLECT/FORALL）、單一 SQL 完成 |
| 統計資訊過期/缺失 | 執行計畫不穩、成本估算離譜 | DBMS_STATS.GATHER_TABLE_STATS 定期收集 |
STATSPACK 或 AWR（10g+）定期快照，建立效能基線；V$SYS_TIME_MODEL、V$SQL 監控腳本，針對 CPU_TIME、ELAPSED_TIME 閾值告警；CPU 過度消耗多源於「大量低效 SQL 並發執行」而非單一「超級 SQL」。診斷關鍵在於：從 OS 層確認資源爭用型態 → 關聯 DB Session → 定位高耗 SQL 模式 → 從演算法/索引/統計資訊三維度優化。本案例中，最終透過為核心查詢新增複合索引並修正統計資訊，CPU 使用率從 97% 降至 15% 以下，系統恢復正常。
