Oracle DBA 效能調優實戰心得：從等待事件與系統資源切入

前言

現代調優核心已轉向「優化等待」。當應用回報緩慢，DBA 常發現命中率符合文件建議，實際瓶頸多集中於 I/O。本文分享 Unix 環境下從 OS 層面到 Oracle 內部的診斷實戰流程。

一、作業系統層面檢查

1. CPU、記憶體、交換、磁碟使用率

工具：sar、vmstat、glance (HP-UX)、topas (AIX)、top (Linux)。

關鍵指標：

• %wio (等待 I/O 佔比) > 35% 表示 I/O 瓶頸；
• %idle 接近 0 表示 CPU 飽和；
• vmstat pi (page in) 非 0 或 free 記憶體極小，代表記憶體壓力；
• glance/topas 記憶體使用率 > 80% 需調整。

2. 記憶體壓力對策

• Oracle SGA 不超過實體記憶體 40-50%；
• 增加實體記憶體；
• 連線數過多改用 MTS (Shared Server)；
• 套用最新 Patch 防止記憶體洩漏。

3. 定位佔用資源最高的 Oracle Session

步驟：

1. OS 層面找出高 CPU 進程 PID (ps -ef | grep ora、glance、topas)；
2. 對應 Oracle Session：

SELECT a.username, a.machine, a.program, a.sid, a.serial#,
       a.status, c.piece, c.sql_text
FROM v$session a, v$process b, v$sqltext c
WHERE b.spid = '<OS_PID>'
  AND b.addr = a.paddr
  AND a.sql_address = c.address(+)
ORDER BY c.piece;

3. 於 PL/SQL Developer/TOAD 分析 SQL 執行計劃，消除全表掃描，減少 I/O 等待。

技巧：將 IN 改寫為 EXISTS，因 Oracle 處理 IN 如 OR，即使有索引也較慢。

4. 找出前 10 大高耗 SQL

SELECT * FROM (
    SELECT parsing_user_id, executions, sorts, command_type,
           disk_reads, sql_text
    FROM v$sqlarea
    ORDER BY disk_reads DESC
) WHERE ROWNUM < 10;

二、Oracle 等待事件診斷

核心視圖：v$session_wait。以下腳本找出正在等待且物理讀最高的 Session：

SELECT se.sid, se.serial#, pr.spid, se.username, se.status,
       se.terminal, se.program, se.module, se.sql_address,
       st.event, st.p1text, si.physical_reads, si.block_changes
FROM v$session se, v$session_wait st, v$sess_io si, v$process pr
WHERE st.sid = se.sid
  AND st.sid = si.sid
  AND se.paddr = pr.addr
  AND se.sid > 6
  AND st.wait_time = 0
  AND st.event NOT LIKE '%SQL%'
ORDER BY physical_reads DESC;

常見等待事件與對策

三、長時間作業監控

v$session_longops 顯示正在執行/已完成的長作業 (如大表掃描、索引建立)，每個 Process 完成刷新一次，可即時掌握進度。

四、並行查詢參數控制

OLTP 環境建議關閉並行查詢：

parallel_min_servers = 0
parallel_max_servers = 0
parallel_automatic_tuning = FALSE

並監控 SQL Hint 是否強制並行，必要時移除 Hint。

結語

調優入口：OS 資源 → 等待事件 → 高耗 SQL → 執行計劃 → 索引/統計/參數調整。建立標準化診斷流程與基線，方能快速定位根因並持續優化。