傳統調優盯著「命中率」、「緩衝區大小」，現代調優核心是 **Wait Interface (等待事件)**。性能問題 90% 根源於 **I/O**。本文分享 Unix 環境下從 OS 層面切入、透過等待事件定位瓶頸的系統性流程。
# 每 2 秒採樣 10 次 (共 20 秒)
sar -u 2 10
| 欄位 | HP-UX | Linux | 含義 | 警戒值 |
|---|---|---|---|---|
| %usr | %usr | %user | 用戶態 CPU | > 70% 可能 CPU 瓶頸 |
| %sys | %sys | %system | 核心態 CPU | > 30% 檢查系統呼叫/鎖爭用 |
| %wio / %iowait | %wio | %iowait (在 %system 中) | **等待 I/O 完成的 CPU 時間** | > 10-15% 明確 I/O 瓶頸 |
| %idle | %idle | %idle | 閒置 CPU | < 10% 系統飽和 |
Linux 2.4.21-20.ELsmp (YY075) 05/19/2005 10:36:07 AM CPU %user %nice %system %iowait %steal %idle 10:36:09 AM all 0.00 0.00 0.13 0.00 0.00 99.87 10:36:11 AM all 0.00 0.00 0.00 0.00 0.00 100.00 10:36:13 AM all 0.25 0.00 0.25 0.00 0.00 99.49
注意： Linux `sar -u` 預設不顯示 `%iowait`，需 `sar -u 2 10` 或 `sar -d` (磁碟) / `iostat -x` 觀察磁碟佇列長度、await、svctm、%util。
vmstat 2 10：`r` (運行佇列)、`b` (阻塞佇列)、`si/so` (swap in/out)、`wa` (iowait)iostat -x 2 10：磁碟層面 `%util`、await、svctmtop/htop/glance：即時進程佔用mpstat -P ALL：多核 CPU 負載均衡
SELECT event, total_waits, time_waited_micro/1000000 AS total_sec,
average_wait/1000 AS avg_ms, wait_class
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited_micro DESC;
| 等待事件 | 等待類別 | 典型原因 | 調優方向 |
|---|---|---|---|
| db file sequential read | User I/O | 索引範圍掃描、單塊讀取 | 優化索引、減少邏輯讀、增加 Buffer Cache |
| db file scattered read | User I/O | 全表掃描、快速全索引掃描 | 避免不必要 FTS、分區裁剪、Materialized View |
| log file sync | Commit | LGWR 寫入 Redo Log 等待 | Redo Log 檔案放快速儲存、檢查 Commit 頻率 |
| log file parallel write | System I/O | LGWR 並行寫入 | 同 log file sync |
| buffer busy waits | Concurrency | 熱塊爭用 (Undo、Segment Header) | 增加 Freelists、Hash Partition、序列快取 |
| enq: TX - row lock contention | Application | 行鎖衝突 | 縮短交易、優化索引避免全表鎖 |
| latch: cache buffers chains | Concurrency | 熱塊 CBC Latch 爭用 | 減少邏輯讀、Hash Partition、_db_block_hash_buckets |
1. OS 層面確認資源飽和度 (CPU/MEM/IO/SWAP)
│
├─ CPU 高 + %wio 低 → 純運算瓶頸 → SQL 優化、平行度調整
├─ %wio 高 → I/O 瓶頸 → 進入步驟 2
└─ Swap 活躍 → 記憶體不足 → 增加實體記憶體、縮減 SGA/PGA
│
2. Oracle 等待事件分析 (V$SYSTEM_EVENT, V$SESSION_WAIT)
│
├─ User I/O 類佔多數 → SQL 調優 (索引、執行計畫、分區)
├─ Concurrency 類佔多數 → 縮短交易、調整鎖/閂參數、應用邏輯
├─ Commit 類佔多數 → Redo Log 配置、批次 Commit
└─ Network/Idle 類 → 檢查客戶端、Array Fetch Size、SDU
│
3. SQL 級深入 (V$SQL, V$SQL_PLAN, V$SQLSTATS)
│
├─ High Buffer Gets → 邏輯讀過高 → 索引、連接順序、統計資訊
├─ High Disk Reads → 物理讀過高 → 緩存、分區、Materialized View
├─ High Rows Processed → 大量資料處理 → 批次化、平行、Direct Path
└─ High Executions → 高頻 SQL → 綁定變數、Cursor Sharing、結果快取
│
4. 物件/段級 (V$SEGSTAT, DBA_HIST_SEG_STAT)
│
├─ 熱表/索引識別 → 移至快速儲存、KEEP Pool、分區
└─ 熱區塊 → Freelist/Hash Partition/Reverse Key Index
│
5. 實施變更 → 驗證 → 持續監控
-- 確認等待事件
SELECT event, time_waited_micro/1000000 sec
FROM v$system_event
WHERE event LIKE 'db file%read'
ORDER BY 2 DESC;
-- 找出高物理讀 SQL
SELECT sql_id, executions, disk_reads/executions AS reads_per_exec,
buffer_gets/executions AS gets_per_exec
FROM v$sqlstats
WHERE disk_reads > 10000
ORDER BY reads_per_exec DESC;
| 層面 | 指標 | 來源 | 告警閾值 |
|---|---|---|---|
| OS | %iowait | sar/vmstat | > 15% |
| OS | 磁碟 %util | iostat | > 80% |
| OS | Swap in/out | vmstat | > 0 (持續) |
| Oracle | DB Time / Elapsed Time | V$SYSMETRIC | > 1.5 |
| Oracle | Logical Reads / Sec | V$SYSMETRIC | 基線 ±50% |
| Oracle | Physical Reads / Sec | V$SYSMETRIC | 基線 ±50% |
| Oracle | Redo Generated / Sec | V$SYSMETRIC | 基線 ±50% |
| Oracle | Parse Count (Hard) / Sec | V$SYSMETRIC | > 100 |
**「不要猜，要測；不要調參數，要調等待」**。從 OS 層面確認資源飽和 → Oracle 等待事件定位瓶頸類別 → SQL/物件級深入 → 精準實施 → 持續驗證。這套 **Wait-Based Tuning** 流程，配合 `sar`/`iostat`/`vmstat` 與 `V$SYSTEM_EVENT`/`V$SQL` 雙層視野，能系統性解決 90% 以上的效能問題。養成「先看 OS，再看 Oracle，最後看 SQL」的診斷肌肉記憶，是資深 DBA 的核心基本功。
