Oracle 10g 自動 SGA 管理 (ASMM) 實戰操作與驗證腳本

Oracle 10g 引入自動共享記憶體管理 (ASMM)，透過單一 SGA_TARGET 參數動態平衡 Shared Pool、DB Cache、Large Pool、Java Pool、Streams Pool。本文提供從手動模式遷移至 ASMM 的標準操作步驟、驗證查詢、動態調整示範與關閉回歸手動模式指令。

遷移至 ASMM 標準流程

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

-- 2. 設定 SGA_TARGET (例：2GB)
ALTER SYSTEM SET sga_target = 2G SCOPE=BOTH;

-- 3. 將自動管理組件參數歸零 (SPFILE 生效)
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;
-- Streams Pool (10gR2) 同理
ALTER SYSTEM SET streams_pool_size = 0 SCOPE=SPFILE;

-- 4. 保留手動管理組件 (視需求設定)
-- ALTER SYSTEM SET log_buffer = ... SCOPE=SPFILE;
-- ALTER SYSTEM SET db_keep_cache_size = ... SCOPE=SPFILE;
-- ALTER SYSTEM SET db_recycle_cache_size = ... SCOPE=SPFILE;

-- 5. 重啟實例使 SPFILE 參數生效
SHUTDOWN IMMEDIATE;
STARTUP;

驗證 ASMM 運作狀態

1. 檢視動態元件目前大小

SELECT component, current_size/1024/1024 AS "Curr_MB",
       min_size/1024/1024 AS "Min_MB",
       max_size/1024/1024 AS "Max_MB",
       user_specified_size/1024/1024 AS "User_MB",
       oper_count, last_oper_type, last_oper_time
FROM v$sga_dynamic_components
WHERE current_size > 0
ORDER BY current_size DESC;

2. 檢視縮放操作歷程

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",
       start_time, end_time
FROM v$sga_resize_ops
ORDER BY start_time DESC;

3. 檢視 Buffer Pool 狀態

SELECT name, block_size, current_size, prev_size, buffers
FROM v$buffer_pool;

4. 快速總覽 SGA 分配

SELECT pool, SUM(bytes)/1024/1024 AS "MB"
FROM v$sgastat
GROUP BY pool
ORDER BY 2 DESC;

動態調整 SGA_TARGET 示範

-- 線上擴大 SGA 至 3GB (無需重啟)
ALTER SYSTEM SET sga_target = 3G SCOPE=BOTH;

-- 觀察自動擴展: Shared Pool、DB Cache 同比例增大
-- 幾分鐘後再查 v$sga_dynamic_components 確認

關閉 ASMM 回歸手動管理

-- 1. 關閉自動管理
ALTER SYSTEM SET sga_target = 0 SCOPE=SPFILE;

-- 2. 重啟前需預先設定各組件目標值 (參考 v$sga_dynamic_components 目前值)
ALTER SYSTEM SET shared_pool_size = 800M SCOPE=SPFILE;
ALTER SYSTEM SET db_cache_size = 1800M SCOPE=SPFILE;
ALTER SYSTEM SET large_pool_size = 50M SCOPE=SPFILE;
ALTER SYSTEM SET java_pool_size = 50M SCOPE=SPFILE;
ALTER SYSTEM SET streams_pool_size = 100M SCOPE=SPFILE;

-- 3. 重啟
SHUTDOWN IMMEDIATE;
STARTUP;

常見問題排查

最佳實務建議

• 生產環境預設啟用 ASMM，僅為關鍵熱/冷表保留 DB_KEEP/RECYCLE_CACHE_SIZE。
• 建立每日排程收集 V$SGA_RESIZE_OPS 統計，季度審視是否需設定最小值。
• 重大版本升級 (10g→11g→19c) 後，重新評估 ASMM 行為 (11g 引入 AMM MEMORY_TARGET)。

ASMM 讓 DBA 從繁瑣的記憶體手工調校中解脫，專注於 SQL 調優與架構演進。掌握遷移、驗證、動態調整、回歸四大操作，即可駕馭自動記憶體管理。