Oracle 性能優化全方位指南：從硬件到 SQL 調優

優化層級優先級（由高到低）

1. 程序設計（根本，設計錯誤無法通過後續調優彌補）
2. 作業系統優化
3. 硬件優化
4. 數據庫優化
5. SQL 語句優化

硬件優化要點

• 增加磁盤數量提升 IOPS，採用 RAID 0+1/5 條帶化均衡 I/O。
• 確保足夠物理內存，避免交換分區頻繁讀寫。

數據庫優化核心

1. 識別負載類型制定策略

• OLTP：高併發、小事務、指標為響應時間與吞吐量。
• OLAP：低併發、大查詢、指標為大數據掃描速度。

兩者優化方向截然不同，切勿混淆。

2. 內存分配黃金比例 2:1:1

• SGA 佔物理內存 50%（上限 70-75%，否則實例不穩定）。
• PGA 佔 25%。
• OS 保留 25%。

3. SGA 子池調優

• Shared Pool：滿足 Library Cache 命中率 >98%、Data Dictionary Cache 命中率 >85%。
• Large Pool：專用服務器 100-200MB；共享服務器按 Session × (Sort_Area_Size+2) 估算。
• Java Pool：不用則不分配。
• Redo Log Buffer：<5MB，日志關鍵，盡快刷盤。
• DB Buffer Cache：越大越好（在 SGA 額度內）。

10g+ 可設置 SGA_TARGET 開啟自動共享內存管理 (ASMM)，由 Oracle 動態調整各池大小。

4. 文件佈局均衡 I/O

Redo Log、Data Files、Archived Logs **分離到不同物理磁盤/磁盤組**，特別是 Redo 與 Archive。

5. Undo Segment 尺寸

根據事務量設置足夠大小，避免「Snapshot too old」錯誤。

6. 熱點對象特殊處理

• 索引段與數據段分離存放。
• 大表採用分區表技術。

7. 索引策略

• 按表特性選擇 B-Tree / Bitmap / Reverse Key Index。
• 單表索引數 ≤5，否則 DML 開銷過大。
• 索引樹高 <4，否則重建（可嘗試 Reverse Key 降低高度）。
• 警惕失效索引（多由劣質 SQL 導致）。

8. 減少全表掃描與排序操作

9. 善用執行計劃定位瓶頸

SQL 層面優化

• 通過 V$LIBRARYCACHE 監控解析命中率，使用綁定變量減少硬解析。
• 採用**物化視圖**預計算複雜聚合，加速查詢。