Oracle 資料庫效能調優全景指南：設計階段規範、運行期 SGA/I/O/鎖調校與工具箱

調優原則與指標體系

核心目標：以最少磁碟 I/O 獲取所需資料

關鍵評估指標：

• 回應時間 vs 吞吐量：OLTP 重回應、DW 重吞吐
• 可用性：MTBF/MTTR、RPO/RTO
• 緩衝區命中率：Buffer Cache > 95%、Library Cache > 90%、Dictionary Cache > 85%
• 記憶體效率：SGA/PGA 分配比例、HugePages 使用率、Swap 為零

階段一：設計開發階段預防性優化

1. 應用架構決策

• 三層架構 (Web/App/DB) + 連接池：集中管理連接、減少 Dedicated Process、支援水平擴展。
• 避免 C/S 兩層直連：連接數失控、邏輯下沉 Client、難以調優。

2. 邏輯/物理結構設計

3. SQL 開發規範 (必須納入 Code Review)

1. 全面綁定變數：禁止 Literal 拼接，啟用 CURSOR_SHARING=EXACT (預設)。
2. 明確列清單：禁 SELECT *，減少網路、Sort 區、Client 解析開銷。
3. 索引友善寫法：
   • 最左前綴原則
   • 避免函數/運算/隱含轉換/負向條件 (!=, NOT IN, IS NULL, LIKE '%...')
   • 必要時建立 Function-Based Index
4. 分頁查詢標準化：12c+ OFFSET ... FETCH，舊版 ROWNUM 子查詢模板。
5. 統計資訊管理：DBMS_STATS.GATHER_TABLE_STATS 定時任務、METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO'、鎖定穩定計畫統計 (LOCK_TABLE_STATS)。

4. 磁碟 I/O 佈局規劃 (部署前完成)

• Redo Log：獨立高速裝置 (NVMe/SSD)、多路歸檔、Group 4~6、大小使切換間隔 15~30 min。
• Undo：獨立表空間、固定大小、RETENTION GUARANTEE (閃回需求)。
• Temp：獨立表空間、多 Tempfile 分散、TEMP_UNDO_ENABLED=TRUE (12c+)。
• 控制檔/參數檔：多路鏡像、異地備份。

階段二：運行期動態調校

1. 作業系統層面

• 消除 Swapping：vmstat 5, sar -p 10 10 監控；配置 HugePages 鎖定 SGA (LOCK_SGA=TRUE)；調整 shmmax, shmall 核心參數。
• 控制 Paging：實體記憶體 ≥ SGA + PGA + OS Reserve (建議 20%)；必要時隔離工作負載。

2. SGA 元件精細調校

3. I/O 與鎖爭用消除

• 避免動態擴展：預分配空間、關閉 AUTOEXTEND 或設 MAXSIZE、DBA_FREE_SPACE 監控。
• 本地管理表空間 (LMT)：EXTENT MANAGEMENT LOCAL AUTOALLOCATE，消除 Data Dictionary Hot Block。
• 鎖/Latch 參數：DB_BLOCK_LRU_LATCHES (CPU 核心數倍數)、DML_LOCKS (依 V$ENQUEUE_STAT 調整)。
• 熱點區塊：V$SEGMENT_STATISTICS 找出 logical reads 高段、考慮 KEEP POOL、READ ONLY 表空間、Reverse Key Index (序列鍵插入熱點)。

4. SQL 執行計畫穩定化

1. SQL Plan Baseline (11g+)：DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 固化優良計畫。
2. SQL Profile：DBMS_SQLTUNE 自動調優產生 Profile。
3. Optimizer Hints：/*+ LEADING(t1 t2) USE_NL(t2) INDEX(t2 idx_col) */ 精確干預。
4. 統計資訊策略：代表性負載時收集、直方圖 SIZE AUTO、增量統計 (11g+)、系統統計 DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL')。

工具箱：從命令列到圖形化

調優迭代流程 (PDCA)

1. Plan：建立效能基線、設定 SLA 目標 (如 P99 < 200ms)。
2. Do：實施單一變更 (參數、索引、SQL 改寫、統計收集)。
3. Check：AWR/ASH 比較變更前後關鍵指標 (DB Time、Logical Reads、CPU、等待事件)。
4. Act：固化有效變更、回滾無效/負面變更、更新知識庫。

結語

Oracle 效能調優是系統工程：設計階段決定上限 (架構、分區、SQL 規範)，運行階段逼近上限 (SGA、I/O、鎖、計畫穩定)。建立標準化開發流程、部署自動化監控基線、持續 PDCA 迭代，並將經驗沉澱為團隊資產，才能讓資料庫長期服務於業務增長。