面對「資料庫慢」，資深 DBA 不會只盯著 SQL。完整調優鏈路：網路 → 硬體 → OS → Oracle 參數 → 資料結構 → 單支 SQL。前三層往往難以變更，故Oracle 參數調校與資料結構優化是最高投報比切入點。本文針對 Unix/Linux 環境，分享系統化調優實戰經驗。
# sar -u 2 10 (每 2 秒採樣 1 次，共 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
...# vmstat 2 10
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
1 0 0 123456 12345 123456 0 0 1 2 10 20 1 0 99 0# Glance / Top / Topas / ps -eo pid,pcpu,pmem,cmd --sort=-pcpu | head
# 找到高 CPU/記憶體的 Oracle 進程 PID (SPID)對應到資料庫 Session 與 SQL：
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 =
AND b.addr = a.paddr
AND a.sql_address = c.address(+)
ORDER BY c.piece; -- ❌ 慢：IN 內部轉 OR，即使有索引也慢
SELECT col1, col2, col3 FROM table1 a
WHERE a.col1 NOT IN (SELECT col1 FROM table2);
-- ✅ 快：NOT EXISTS 可使用反連結、半連結優化
SELECT col1, col2, col3 FROM table1 a
WHERE NOT EXISTS (
SELECT 1 FROM table2 b WHERE a.col1 = b.col1
);-- 10g+ 建議 DBMS_STATS (支援並行、直方圖、增量統計)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SCHEMA',
tabname => 'BIG_TABLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
cascade => TRUE,
degree => 4
);
-- 檢查最後分析時間
SELECT table_name, last_analyzed FROM dba_tables WHERE owner = 'SCHEMA';
SELECT index_name, last_analyzed FROM dba_indexes WHERE table_owner = 'SCHEMA';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;查詢 V$SESSION_WAIT 找出系統在等什麼：
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 si.sid = se.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;| 等待事件 | 含義 | 對策 |
|---|---|---|
| buffer busy waits / free buffer waits | DBWR 寫入跟不上、緩衝區爭用 | 增加 DB_WRITER_PROCESSES (如 4)、同步增大 DB_BLOCK_LRU_LATCHES (如 8)、啟用非同步 I/O |
| db file sequential read | 單塊讀 (索引存取、單塊表存取) | 優化 SQL 走索引、分區裁剪、增大 DB_FILE_MULTIBLOCK_READ_COUNT |
| db file scattered read | 多塊讀 (全表掃描、快速全索引掃描) | 建索引、分區、MV、增大 DB_FILE_MULTIBLOCK_READ_COUNT |
| latch free | 共用池/緩衝區 Latch 爭用 | 增大 Shared Pool、使用綁定變數、Cursor Sharing、減少硬解析 |
| log file sync | Commit 等待 LGWR 寫入 Redo | Redo Log 放快速儲存、批次 Commit、檢查 LGWR 效能 |
| enq: TX - row lock contention | 行鎖競爭 | 縮短交易、外鍵加索引、邏輯拆分熱點列 |
