Oracle 效能調校全方位指南：參數、記憶體、I/O、爭用與實戰策略

編輯：Oracle 資料庫基礎

調校目標設定

效能調校非單一動作，而是針對明確目標的系統性工程。常見目標：

參數	說明	調校方向
`SGA_TARGET` / `SGA_MAX_SIZE`	SGA 總大小 (自動共享記憶體管理 ASMM)	實體記憶體 40%~60%，留足 OS/PGA 空間
`DB_CACHE_SIZE`	預設緩衝區快取 (手動管理時)	配合 `DB_16K_CACHE_SIZE` 等多區塊大小
`SHARED_POOL_SIZE`	共用池大小 (Library/Data Dictionary Cache)	監控 `V$SGASTAT` free memory，避免 ORA-04031
`LARGE_POOL_SIZE`	大型池 (RMAN、共用伺服器、平行查詢)	若使用 MTS/RMAN 平行備份需適度配置
`JAVA_POOL_SIZE`	Java 池	若無 Java Stored Procedure 可設最小值
`PGA_AGGREGATE_TARGET`	PGA 總量目標 (自動 PGA 管理)	實體記憶體 20%~30%，監控 `V$PGA_TARGET_ADVICE`
`DB_FILE_MULTIBLOCK_READ_COUNT`	全表掃描多區塊讀取數	預設 128 (1MB)，視儲存裝置最佳化
`LOG_BUFFER`	重做日誌緩衝區	通常 1MB~100MB，觀察 `log buffer space` 等待
`CHECKPOINT_PROCESS`	啟用 CKPT 背景程序	TRUE (現代版本預設)
`FAST_START_MTTR_TARGET`	目標平均復原時間 (秒)	依業務 RTO 設定，間接控制 checkpoint 頻率
`OPTIMIZER_MODE`	優化器模式	ALL_ROWS (OLAP) / FIRST_ROWS_n (OLTP)
`CURSOR_SHARING`	游標共用策略	EXACT (預設) / FORCE (強制字面值轉綁定，需測試)

啟用 AMM (Automatic Memory Management)：MEMORY_TARGET + MEMORY_MAX_TARGET，Oracle 自動在 SGA/PGA 間動態調配 (11g+)。
若需精細控制：關閉 AMM，啟用 ASMM (SGA_TARGET) + 自動 PGA (PGA_AGGREGATE_TARGET)。
監控關鍵指標：
- Buffer Cache Hit Ratio > 99% (OLTP) / V$SYSSTAT physical reads vs db block gets
- Library Cache Hit Ratio > 99% (硬解析率 < 1%)
- PGA Cache Hit % > 90% (V$PGA_TARGET_ADVICE)
- 避免 ORA-04031 (shared pool 碎片/不足)
調整結果驗證：AWR/ASH 報告對比調校前後的 DB Time、Logical Reads、Physical Reads、Hard Parses。

檔案分散：資料表空間、索引表空間、UNDO、TEMP、Redo Log、Control File 分置於不同實體磁碟/ASM Disk Group/存儲 LUN。
Redo Log 多工：每組至少 2 成員，置於不同控制器/磁碟；組數視切換頻率調整 (建議切換間隔 15~30 分鐘)。
ASM 最佳實務：Normal/High Redundancy、AU_SIZE 4MB、Allocation Unit 連續、啟用 DB_BLOCK_CHECKSUM。
Direct Path Read/Write：大型平行查詢、排序、雜湊聯結、RMAN 備份自動使用；確保 FILESYSTEMIO_OPTIONS=SETALL (非 ASM 時)。
監控等待事件：db file scattered read (全表掃描)、db file sequential read (索引讀取)、log file sync (Commit 等待)、log file parallel write。

爭用類型	典型等待事件	對策
Library Cache Lock/Pin	library cache lock, library cache pin	使用綁定變數、減少 DDL、增大 SHARED_POOL、啟用 Cursor Sharing
Row Cache Lock	row cache lock	增大 SHARED_POOL、固定熱門物件 (DBMS_SHARED_POOL.KEEP)
序列鎖	enq: SQ - contention	`CACHE 1000`、`ORDER/NOORDER` 視需求、考慮可擴展序列 (12c+)
熱塊	buffer busy waits, gc buffer busy (RAC)	反向鍵索引、分區表、增加 PCTFREE、Hash Partition
TX 鎖 (行鎖)	enq: TX - row lock contention	縮短交易、避免長交易、索引 FK 欄位、應用層樂觀鎖

建立基線：AWR 快照保留 30+ 天，固定時間匯出基線報告。
識別 Top SQL：V$SQL / AWR SQL ordered by Elapsed Time / Gets / Executions。
執行計畫分析：DBMS_XPLAN.DISPLAY_CURSOR、SQL Monitoring、Real-Time SQL Monitoring。
索引/統計資訊/重寫 SQL：建立/刪除索引、DBMS_STATS.GATHER_TABLE_STATS、SQL Profile/SPM Baseline。
參數微調：依等待事件調整上述參數，單一變因、觀察效果。
自動化巡檢：Shell/Python 腳本定期抓取關鍵指標，異常告警 (Enterprise Manager / Cloud Control / 自建 Grafana)。