Unix 環境 Oracle 效能調優實戰：從 OS 層到 SQL 層的系統化排查方法

核心理念：現代調優 = 調優 Waits（等待事件）

傳統看命中率已不足，**I/O 為性能最大瓶頸**。從 OS 層等待發現資料庫與系統資源配置不合理。

第一層：OS 資源檢查

CPU：sar -u 2 10

%usr %sys %wio %idle
%wio > 35% → I/O 瓶頸，CPU 在等待磁碟
%idle ≈ 0 → CPU 飽和，需檢查高佔用進程

Linux %system 近似 %wio。

記憶體：vmstat / glance (HP-UX) / topas (AIX)

• pi (page in) ≠ 0 → 換頁發生。
• free 極小、記憶體利用率 > 80% → 記憶體壓力。
• 對策：SGA+PGA ≤ 物理記憶體 40-50%，啟用 AMM/ASMM、增加記憶體、改用 MTS (Shared Server) 減少 PGA、打補丁防記憶體洩漏。

磁盤 I/O：iostat -x / sar -d

• %util 接近 100%、await > 20ms、svctm 高 → 磁碟飽和。
• 對策：OS 層條帶化 (LVM/RAID 0+1)、資料檔/重做日誌/歸檔/備份分盤、ASM 平均分佈、熱表移至快閃儲存。

第二層：定位高消耗 Session 及 SQL

-- 取得 OS 進程號 (PID/SPID) 後
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;

• 在 PL/SQL Developer / TOAD / SQLcl 中執行上述 SQL，替換 <OS_PID>。
• 取得完整 SQL 文本，進入 SQL 層調優。

第三層：SQL 調優標準流程

1. 收集統計資訊（DBMS_STATS）。
2. 查看執行計畫（EXPLAIN PLAN / DBMS_XPLAN.DISPLAY_CURSOR）。
3. 檢查：全表掃描是否必要、索引缺失、連接順序、基數估算偏差。
4. 優化手段：建立/調整索引、重寫 SQL、Hint、分區、物化視圖、SQL Profile/Baseline。
5. 驗證：對比調優前後 Elapsed Time、Buffer Gets、Disk Reads、Rows Processed。

常用 OS 工具速查