Oracle 10g SGA (系統全域區) 架構、記憶體顆粒與自動共享記憶體管理深度解析

System Global Area (SGA) 是 Oracle 執行個體核心共享記憶體結構。Oracle 10g 引入自動共享記憶體管理 (ASMM)，透過 SGA_TARGET 參數動態調整各組件大小。本文詳細解析 SGA 組件、記憶體顆粒機制、SGA_MAX_SIZE 硬性上限、ASMM 啟用條件與手動/自動模式切換實務。

SGA 核心組件對照

記憶體顆粒

• SGA 記憶體分配/回收最小單位 = 顆粒。
• 顆粒大小由 SGA_MAX_SIZE 決定：
  • ≤ 1 GB → 4 MB
  • > 1 GB → 16 MB (32-bit Windows 例外：8 MB)
• 查詢目前顆粒大小：SELECT * FROM V$SGAINFO WHERE NAME = 'Granule Size';
• 若參數設定非顆粒倍數，Oracle 會四捨五入至最接近倍數 (如顆粒 4MB，設 10MB → 實際配 12MB)。

SGA_MAX_SIZE 硬性上限

• 靜態參數，啟動後無法動態修改，需重啟實例生效。
• 未指定時，預設 = 所有手動參數初始值總和。
• SGA_TARGET 動態調整上限 = SGA_MAX_SIZE。

啟用自動共享記憶體管理 (ASMM)

-- 1. 確認 STATISTICS_LEVEL
SHOW PARAMETER statistics_level;  -- 必須 TYPICAL 或 ALL

-- 2. 設定 SGA_TARGET > 0 (單位：Bytes/K/M/G)
ALTER SYSTEM SET sga_target = 2G SCOPE=BOTH;

-- 3. 將自動管理組件參數設為 0 (或移除)
ALTER SYSTEM SET shared_pool_size = 0 SCOPE=SPFILE;
ALTER SYSTEM SET large_pool_size = 0 SCOPE=SPFILE;
ALTER SYSTEM SET java_pool_size = 0 SCOPE=SPFILE;
ALTER SYSTEM SET db_cache_size = 0 SCOPE=SPFILE;
-- 手動組件 (LOG_BUFFER, DB_KEEP_CACHE_SIZE 等) 保留原值

-- 4. 重啟實例 (若參數用 SPFILE SCOPE=SPFILE)
SHUTDOWN IMMEDIATE; STARTUP;

驗證 ASMM 運作

-- 檢視各組件目前大小
SELECT component, current_size/1024/1024 AS "MB" FROM v$sga_dynamic_components;

-- 檢視動態調整歷程
SELECT component, oper_type, oper_mode, parameter, initial_size/1024/1024 AS "Init MB",
       target_size/1024/1024 AS "Target MB", final_size/1024/1024 AS "Final MB"
FROM v$sga_resize_ops ORDER BY start_time DESC;

手動模式試算 SGA_TARGET 參考值

SELECT (
  (SELECT SUM(value) FROM v$sga) -
  (SELECT current_size FROM v$sga_dynamic_free_memory)
) AS "SGA_TARGET" FROM dual;

關閉 ASMM 回歸手動管理

ALTER SYSTEM SET sga_target = 0 SCOPE=SPFILE;
-- 重啟後需手動設定各組件大小

最佳實務建議

• 生產環境優先啟用 ASMM，減少人工調校，應對負載變化。
• 保留 DB_KEEP_CACHE_SIZE/DB_RECYCLE_CACHE_SIZE 手動管理關鍵熱/冷表。
• 監控 V$SGA_RESIZE_OPS，若頻繁縮放同一組件，考慮給該組件最小值 (如 SHARED_POOL_SIZE=200M) 避免震盪。
• 32-bit 系統若需 > 2GB SGA，需設定 USE_INDIRECT_DATA_BUFFERS=TRUE (參見 93087)。

掌握 SGA 顆粒機制與 ASMM 運作邏輯，能讓 DBA 從繁瑣的記憶體手動調校中解脫，專注於 SQL 調優與架構優化。