Oracle 調優非單一參數微調,而是跨越「外部環境 → 物理存取 → SQL → 排序 → 爭用」五大層次的系統工程。若外部資源(CPU/記憶體/網路)成瓶頸,任何內部調優皆事倍功半。
| 資源 | 瓶頸徵兆 | 對策 |
|---|---|---|
| CPU | Run Queue > CPU 核心數;vmstat sy/time 高 | 增加 CPU、關閉 Parallel Query、優化高 CPU SQL |
| 記憶體 | Paging/Swapping 頻繁;vmstat pi/po 持續非零 | 加記憶體、縮減 SGA、關閉 MTS/Shared Server |
| 網路 | Net8/TCP 封包重傳高;SQL*Net wait 事件佔比大 | 啟用 SDU/TCPIP.NODELAY、就近部署、連線池 |
工具:vmstat、top、sar、glance、nmon。目標:確保 OS 層面資源充足、無爭用。
將表資料依查詢順序(如索引鍵)重排,使相關行集中在同一區塊,單次多塊讀取即可滿足查詢,大幅降低 db file scattered read。手段:ALTER TABLE ... MOVE、CTAS、DBMS_REDEFINITION、分區表。
OPTIMIZER_MODE=ALL_ROWS 或 FIRST_ROWS_n,避免 CHOOSE。DBMS_STATS 定期收集,Histogram 用於斜態欄位。EXISTS 替代 IN、UNION ALL 替代 UNION、WITH 子查詢物化、避免 SELECT *。SORT_AREA_SIZE / PGA_AGGREGATE_TARGET 適度調大,減少磁碟排序。INDEX ... ORDER BY 消除 Sort 操作。DISTINCT/GROUP BY,改用應用層去重或物化視圖。| 爭用類型 | 典型等待事件 | 對策 |
|---|---|---|
| Buffer Busy Waits | buffer busy waits | 增加 freelists/freelist groups、使用 ASSM、分區 |
| Enqueue/Lock | enq: TX - row lock contention | 縮短交易、降低隔離級別、索引外鍵 |
| Latch | latch: cache buffers chains | 減少熱塊競爭、調大 _db_block_hash_buckets |
| Redo Log | log file sync, log file switch | 調大 Redo Log、多組多工、快速儲存裝置 |