Oracle 記憶體參數調優實戰：透過 PFILE/SPFILE 降低 SGA 佔用

開發/測試環境資源有限，需縮減 Oracle SGA 佔用以騰出記憶體給作業系統或其他服務。本文提供標準流程：`CREATE PFILE FROM SPFILE` → 編輯參數 → `SHUTDOWN` → `CREATE SPFILE FROM PFILE` → `STARTUP`，並給出 2GB 實體記憶體環境下的具體參數參考值與驗證腳本。

標準調整流程 (需 SYSDBA)

-- 1. 以 SYSDBA 登入
SQLPLUS "/ AS SYSDBA"

-- 2. 從 SPFILE 建立可編輯的 PFILE (文字檔)
CREATE PFILE = 'D:/cj.ora' FROM SPFILE;

-- 3. 使用文字編輯器修改 D:/cj.ora 參數 (見下表)

-- 4. 關閉資料庫
SHUTDOWN IMMEDIATE;

-- 5. 從修改後的 PFILE 重建 SPFILE
CREATE SPFILE FROM PFILE = 'D:/cj2.ora';

-- 6. 啟動資料庫
STARTUP;

-- 7. 驗證
SHOW SGA;
SHOW PARAMETER SGA;

2GB 實體記憶體環境參數參考 (調整前 vs 調整後)

關鍵參數設定原則

• SGA_TARGET + PGA_AGGREGATE_TARGET < 實體記憶體 × 70%： 留足 OS 檔案系統緩存、網路緩衝、其他進程空間。
• SGA_MAX_SIZE ≥ SGA_TARGET： 硬上限不小於動態目標。
• 元件最小值 (_SIZE 參數)： 在 ASMM 下作為下限保障，防止被壓縮過度。
• 避免設定 MEMORY_TARGET (AMM)： 開發機 `/dev/shm` 通常較小，AMM 易導致 ORA-00845 (MEMORY_TARGET not supported)。

驗證腳本

-- 1. 確認 SGA 總量
SHOW SGA;

-- 2. 確認各組件實際大小
SELECT component, current_size/1024/1024 AS mb
FROM   V$SGA_DYNAMIC_COMPONENTS
WHERE  current_size > 0
ORDER  BY current_size DESC;

-- 3. 確認參數生效
SHOW PARAMETER SGA;
SHOW PARAMETER PGA;

-- 4. 監控實際記憶體使用 (OS 層面)
-- Linux: pidstat -r -p  1
-- 或: cat /proc//status | grep VmRSS

常見問題與對策

自動化調整腳本 (Shell)

#!/bin/bash
# reduce_sga.sh - 開發環境一鍵縮減 SGA
ORACLE_SID=ORCL
PFILE_DIR=/tmp
TARGET_SGA_MB=512

sqlplus -s "/ as sysdba" <