Oracle 效能調校實戰心法：從 OS 層面鎖定 IO 瓶頸到 SQL 重寫技巧

調校思維轉型：優化等待事件，而非命中率

傳統迷信緩衝區命中率、資料庫參數合規，實則現代調校核心在於分析等待事件。效能瓶頸 80% 集中在 IO，透過作業系統工具與 Oracle 等待介面定位問題，事半功倍。本文以 Unix/Linux 環境為主。

第一層：作業系統資源檢視

CPU 與 IO：sar -u

# 每 2 秒採樣 10 次
sar -u 2 10

關鍵欄位：

• %wio (或 Linux %system)：等待 IO 完成的 CPU 百分比。持續超過 35% 表示 IO 瓶頸。
• %idle：閒置百分比。接近 0 代表 CPU 飽和。

範例中 %wio 平均 11%（正常），但 %idle 為 0，顯示 CPU 為主要瓶頸。

記憶體：vmstat / glance / topas

• vmstat pi (page in) 非零、free 極小 → 記憶體壓力。
• glance/topas 記憶體使用率 > 80% 需關注。

對策：

1. Oracle SGA 不超過實體記憶體 40~50%。
2. 增加實體記憶體。
3. 連線數極多時採用 MTS (Shared Server)。
4. 套用修補程式防止記憶體洩漏。

第二層：鎖定高資源消耗 Session 與 SQL

找出佔用 CPU/IO 最高的 OS Process

使用 glance、top、ps -ef 取得 PID，再對應 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;

SQL 重寫關鍵技巧：IN → NOT EXISTS

Oracle 處理 IN 子查詢採用 OR 展開，即使有索引也慢。改寫為 NOT EXISTS 可大幅降低 IO：

-- 原始：全表掃描風險高
SELECT col1, col2, col3 FROM table1 a
WHERE a.col1 NOT IN (SELECT col1 FROM table2);

-- 優化：半連接，可用索引
SELECT col1, col2, col3 FROM table1 a
WHERE NOT EXISTS (SELECT 'x' FROM table2 b WHERE a.col1 = b.col1);

第三層：系統級等待事件分析 (v$session_wait)

SELECT se.sid, se.serial#, pr.spid, se.username, se.status,
       se.program, se.module, st.event,
       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 si.physical_reads DESC;

常見等待事件對策表

快速定位前 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;

總結

效能調校是「由外而內、由待而因」的系統工程：OS 資源 → 等待事件 → 高消耗 Session → SQL 文本 → 執行計畫 → 存取路徑 → 物理設計。建立標準化排查流程，比單一參數調整更可靠。