Oracle SGA 與 PGA 記憶體調優實戰：從架構理解到參數優化

核心觀念：SGA 與 PGA 的角色分工

Oracle 實例記憶體架構分為兩大塊：SGA（System Global Area）為所有背景程序與前景程序共享，PGA（Program Global Area）則為每個伺服器程序專用。合理分配兩者比例，是效能調優的基石。

實際案例環境快照

關鍵原則：總記憶體不超實體上限

SGA + PGA < 實體記憶體 - OS/其他程序佔用。違反此原則會導致作業系統交換，效能劇降。

SGA 三大核心組件深度解析

1. 共用池 - SQL 與資料字典的核心快取

• Library Cache：儲存已解析 SQL/PLSQL 程式碼（共用 SQL 區），減少硬解析
• Data Dictionary Cache：快取資料字典資訊（使用者、表、權限等）

共用池過小會導致 ORA-04031 或頻繁硬解析；過大則擠佔資料緩衝區。

2. 資料緩衝區 - 磁碟 I/O 的第一道防線

採用 LRU 演算法管理，包含 DEFAULT、KEEP、RECYCLE 三大池。DB_CACHE_SIZE 直接影響邏輯讀/物理讀比率。

3. 重做日誌緩衝區 - 交易持久化的加速器

由 LOG_BUFFER 控制，大量 DML 環境建議適度放大，減少 log file sync 等待。

PGA 管理策略：自動 vs 手動

• 自動模式（推薦）：設定 PGA_AGGREGATE_TARGET，Oracle 自動分配排序、雜湊、位圖建立等作業區
• 手動模式：逐一設定 SORT_AREA_SIZE、HASH_AREA_SIZE 等，維護成本高

監控 V$PGASTAT 中 cache hit percentage，若長期低於 90% 需增加 PGA 目標值。

調優檢查清單

1. 確認 SGA_MAX_SIZE 足夠支援未來動態成長
2. 依 V$SGASTAT 觀察共用池空閒記憶體，避免碎片化
3. 啟用 DB_CACHE_ADVICE 模擬不同緩衝區大小效益
4. 定期檢視 V$PGA_TARGET_ADVICE 獲取 PGA 最佳建議值
5. 確保 STATISTICS_LEVEL = TYPICAL 以上，否則顧問功能失效

記憶體調優非一次完成，需建立基線、持續監控、逐步微調。掌握 SGA/PGA 運作機制，才能精準診斷瓶頸、釋放硬體最大效能。