診斷思維：由整體至局部、由面到點
性能問題千頭萬緒，切忌上來就調 SQL。標準流程四步驟：
- 蒐集用戶痛點：操作慢、並發高 CPU/記憶體滿載、死鎖等。
- 釐清最迫切問題：優先解決業務影響最大者。
- 根因分析：唯有找到根因，調優才有的放矢。
- 驗證與固化：調整後壓測確認，納入變更管理。
第一層：伺服器層面（硬體 + OS）
硬體資源檢查
- CPU：
top、vmstat、mpstat 觀察 run queue、context switch、steal time。
- 記憶體：
free -h、vmstat 關注 swap 活動、page in/out。
- 儲存：
iostat -xmt 5 監控 %util、await、svctm；專用工具測試磁碟頻寬。
若硬體瓶頸明確（如 I/O 等待持續 > 20ms、CPU 長期飽和），優先擴容或調整架構，資料庫層面調優效果有限。
作業系統調校
- Linux：核心參數
vm.swappiness、vm.dirty_ratio、hugepages、檔案描述符限制、/etc/security/limits.conf。
- Windows：效能監視器觀察 Pages/sec、Available MBytes、Pool Nonpaged Bytes。
第二層：資料庫整體架構規劃
儲存規劃
- 資料檔、索引檔、控制檔、Redo Log 多路徑分離不同磁碟/ASM 磁碟組。
- 應用系統獨立表空間，大物件 (LOB) 獨立表空間。
記憶體規劃
| 元件 | 建議 |
|---|
| SGA_TARGET / MEMORY_TARGET | 啟用自動共享記憶體管理 (ASMM/AMM) |
| DB_CACHE_SIZE | 盡量佔大頭，提升緩衝命中率 |
| PGA_AGGREGATE_TARGET | 排序/雜湊密集系統適當加大 |
| SHARED_POOL | 80M~300M 視 CPU 核心數而定，多核可適度減小 |
| JAVA_POOL, LARGE_POOL | 各 32M 通常足夠 |
實例參數審視
- 優化器模式：
OPTIMIZER_MODE、統計收集頻率 (DBMS_STATS 排程)。
- Cursor Sharing：
CURSOR_SHARING = FORCE/SIMILAR 權衡。
- Pre-page SGA：
PRE_PAGE_SGA 避免啟動期分頁錯誤。
第三層：統合診斷工具 — Statspack / AWR
安裝 Statspack 或啟用 AWR（需診斷包授權），設定擷取間隔（建議 30~60 分鐘）。報告關注：
- Load Profile：每秒交易、邏輯讀、物理讀、Redo 大小。
- Instance Efficiency：緩衝命中率、Library Cache 命中率、Soft Parse %。
- Top 5 Wait Events：系統級等待熱點。
- Top SQL：按 Elapsed Time、Buffer Gets、Executions 排序的問題 SQL。
第四層：局部 SQL 與應用層定位
- 依用戶回報操作，啟用
SQL TRACE (10046) 或 DBMS_MONITOR 擷取執行計畫與等待事件。
TKPROF 格式化 Trace 檔，聚焦高耗時 SQL。- 分析執行計畫：Cardinality 偏差、存取路徑、Join Order、Partition Pruning。
- 索引檢查：是否缺索引？是否有未用索引 (
ALTER INDEX ... MONITORING USAGE)？
- 並發問題：死鎖 Trace (10047)、隔離等級、ITL 等待、熱塊競爭。
常見遺漏檢查項
- 優化器模式是否為 CBO？統計資訊是否最新？直方圖是否收集？
- 索引監控是否發現長期未用索引？
- 應用端連線池設定是否導致 Session 暴增？
結語
性能診斷如醫生問診：先量體溫、血壓（伺服器/OS），再看血液報告（AWR/Statspack），最後針對病灶開刀（SQL/索引/應用）。跳過前兩層直奔 SQL，往往治標不治本。
- 上一篇文章： Oracle 多表查詢優化：WHERE 條件順序、SELECT * 陷阱與減少資料庫往返
- 下一篇文章： Oracle 監聽器端口修改完整操作手冊與安全加固
Copyright ©
程式碼之家 All Rights Reserved
