Oracle 記憶體架構深度解析：SGA、PGA、大池、Java 池、排序區參數與監控

Oracle 記憶體架構深度解析：SGA、PGA、大池、Java 池、排序區參數與監控

Oracle 記憶體結構直接決定資料庫效能上限。本文深度剖析 SGA (系統全域區)、PGA (程式全域區)、排序區、大池、Java 池五大元件的功能、關鍵參數、查詢指令與調校原則，助 DMA 從記憶體層面定位瓶頸、規劃容量。

一、記憶體元件總覽

二、SGA 三大核心子結構

1. 資料緩衝區 (Database Buffer Cache)：最近存取資料區塊，LRU 淘汰。大小由 DB_CACHE_SIZE (手動) 或 SGA_TARGET (自動) 控制。查詢：SHOW PARAMETER DB_CACHE_SIZE。
2. 重做日誌緩衝區 (Redo Log Buffer)：交易變更向量，LGWR 定期寫入線上重做日誌檔。大小 LOG_BUFFER (通常自動計算)。查詢：SHOW PARAMETER LOG_BUFFER。
3. 共用池：
   - Library Cache：SQL/PLSQL 解析樹、執行計劃。
   - Data Dictionary Cache：表/欄位/權限定義。
   - Server Result Cache (11g+)：查詢結果快取。
   大小 SHARED_POOL_SIZE。查詢：SHOW PARAMETER SHARED_POOL_SIZE。

三、PGA 與自動工作區管理

• PGA_AGGREGATE_TARGET 設定實例級 PGA 總量目標，Oracle 自動分配給各 Session 的排序/雜湊/位圖工作區。
• 12c 新增 PGA_AGGREGATE_LIMIT 硬性上限，防止失控查詢耗盡記憶體導致 OOM。
• 查詢：SHOW PARAMETER PGA_AGGREGATE_TARGET。

四、排序區演進

• 手動模式 (WORKAREA_SIZE_POLICY=MANUAL)：SORT_AREA_SIZE、HASH_AREA_SIZE 固定大小，易造成記憶體浪費或溢寫。
• 自動模式 (AUTO，預設)：Oracle 依 PGA_AGGREGATE_TARGET 動態分配，最佳化記憶體利用率。
• 查詢：SHOW PARAMETER SORT_AREA_SIZE (僅手動模式生效)。

五、大池與 Java 池調校

• 大池：啟用 RMAN 平行備份、共用伺服器、平行查詢時建議設定 50-200MB。SHOW PARAMETER LARGE_POOL_SIZE。
• Java 池：若資料庫部署 Java Stored Procedure，依應用規模設定 20-100MB。SHOW PARAMETER JAVA_POOL_SIZE。

六、記憶體監控關鍵視圖

-- SGA 元件大小與動態調整歷程
SELECT COMPONENT, CURRENT_SIZE/1024/1024 MB, LAST_OPER_TYPE, LAST_OPER_TIME
FROM V$SGA_DYNAMIC_COMPONENTS;

-- PGA 使用狀況
SELECT NAME, VALUE/1024/1024 MB FROM V$PGASTAT WHERE NAME IN ('aggregate PGA target parameter','total PGA allocated','maximum PGA allocated');

-- 共用池碎片與重載
SELECT * FROM V$LIBRARYCACHE WHERE RELOADS > 0;

-- 排序區溢寫臨時表空間統計
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('sorts (memory)','sorts (disk)');

七、調校黃金法則

1. 啟用 ASMM (SGA_TARGET) + AMM (MEMORY_TARGET 11g+) 讓 Oracle 自動平衡 SGA/PGA。
2. 保留 20-30% 實體記憶體給 OS、檔案系統快取、網路緩衝。
3. 監控 V$SGA_RESIZE_OPS 確認動態調整頻率不過高 (每小時 < 5 次)。
4. 大池、Java 池僅在實際用到功能時設定，避免佔用 SGA 空間擠壓 Buffer Cache。
5. 定期收集 AWR 報表對比 Buffer Cache Hit Ratio、Library Cache Hit Ratio、PGA Cache Hit % 趨勢。

精準掌握記憶體架構與參數，是從「猜測調校」走向「資料驅動調校」的關鍵。