Oracle 10g 記憶體架構完結篇：PGA、排序區、大池、Java 池與自動共享記憶體管理 (ASMM) 詳解

一、程序全局區 (PGA)

• 單一用戶/服務器程序專用記憶體，非共享
• 用戶連線建立 Session 時自動分配，斷開時釋放
• 主要用途：儲存用戶程式變數、陣列 (STACK SPACE)、Session 額外資料 (USER SESSION DATA)

二、排序區 (Sort Area)

• 為 ORDER BY、GROUP BY、索引建立等排序操作提供記憶體
• 優先使用記憶體排序；不足時自動溢寫至臨時表空間 (磁碟排序)
• 關鍵參數：SORT_AREA_SIZE、SORT_AREA_RETAINED_SIZE

參數查詢三法

-- 參數檔
SORT_AREA_SIZE=65536
SORT_AREA_RETAINED_SIZE=65536

-- SQL 查詢
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE '%SIZE';
SHOW PARAMETER SORT_AREA_SIZE;

排序使用情況監控

SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%SORT%';

關鍵指標：SORTS (MEMORY) vs SORTS (DISK)，磁碟排序比例過高需增大排序區或優化 SQL。

三、大池

• 專供 RMAN 備份/恢復、共用伺服器 UGA、平行執行訊息緩衝等大型記憶體分配
• 避免大型分配碎片化共享池
• 參數：LARGE_POOL_SIZE，查詢：SHOW PARAMETER LARGE_POOL_SIZE

四、Java 池

• 支援資料庫內部 Java 虛擬機執行 Java 程式碼
• 參數：JAVA_POOL_SIZE，建議 不小於 20MB

五、Oracle 10g 自動共享記憶體管理 (ASMM)

啟用條件

• STATISTICS_LEVEL = TYPICAL (預設) 或 ALL
• 設定 SGA_TARGET 指定 SGA 總大小
• 無需手動設定 SHARED_POOL_SIZE、DB_CACHE_SIZE 等，Oracle 自動動態分配
• 若未設定 SGA_TARGET，ASMM 停用，退回手動模式

最小值保護機制

DBA 可為關鍵組件設定最小值，自動分配時不低於此值：

SGA_TARGET = 256M
SHARED_POOL_SIZE = 32M      -- 最小保障
DB_CACHE_SIZE = 100M        -- 最小保障
-- 剩餘 124M 由 Oracle 動態分配給其他組件

查詢動態分配結果：SELECT * FROM V$SGA_DYNAMIC_COMPONENTS;

手動覆蓋行為

• 若手動設定值 > 目前組件值 → 組件增大至手動值
• 若手動設定值 < 目前組件值 → 不立即生效，需等待自動調整釋放

完全由 DBA 手動管理的參數（ASMM 不自動調整）

• LOG_BUFFER
• DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE
• DB_nK_CACHE_SIZE
• STREAMS_POOL_SIZE

設定上述參數會減少可供自動分配的記憶體池。

六、從手動管理遷移至 ASMM

步驟

1. 計算目前 SGA 總大小：SELECT SUM(VALUE) FROM V$SGA (含 Fixed SGA ~16M)
2. 或查詢可用記憶體：SELECT (SUM(VALUE) - (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)) FROM V$SGA
3. 從參數檔移除手動組件參數 (SHARED_POOL_SIZE, DB_CACHE_SIZE, LARGE_POOL_SIZE 等)
4. 新增 SGA_TARGET = 計算值
5. 建議使用 SPFILE，關機時會保留上次動態調整值，重啟後延續最佳配置

遷移範例

-- 手動參數
SHARED_POOL_SIZE = 200M
DB_CACHE_SIZE = 500M
LARGE_POOL_SIZE = 200M
LOG_BUFFER = 16M
-- SGA_MAX_SIZE = 1200M

-- 查詢
SELECT SUM(VALUE) FROM V$SGA;           -- 1200M
SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY;  -- 208M

-- SGA_TARGET = 1200 - 208 = 902M

總結

10g ASMM 透過 SGA_TARGET 實現 SGA 組件動態平衡，大幅降低手動調校負擔。理解 PGA/排序區/大池/Java 池各自定位，配合 V$SGA_DYNAMIC_COMPONENTS 監控，能在自動化基礎上進行精準干預。遷移時務必用 SPFILE 保留調優成果。