Oracle 9i 動態 SGA/PGA 管理深度解析與自動調優腳本

9i 記憶體管理革命

Oracle 9i 引入 動態 SGA 与 自動 PGA 管理，所有 SGA 組件（共享池、緩沖池、大池、Java 池、Redo Log Buffer、PGA 彙總目標）均可通過 ALTER SYSTEM 線上調整，無需重啟實例。配合 V$ 動態視圖，實現「監控→調整→再監控」閉環。

SGA 組件調整策略

自動 PGA 管理機制

• WORKAREA_SIZE_POLICY = AUTO（默認）啟用自動模式
• PGA_AGGREGATE_TARGET 限制所有會話 PGA 總量（10M ~ 4PB）
• 串行操作單 SQL 內存上限：MIN(5% * 目標, 100MB)
• 並行操作單 SQL 內存上限：30% * 目標 / DOP（10g 後改為 50%）
• 可調內存 + 不可調內存 ≤ 目標值，不可調部分佔用過大時自動管理失效

建議初始值（單純 DB 服務器）

• 保留 20% 物理內存給 OS
• OLTP：PGA = 剩餘 80% × 20%
• DSS：PGA = 剩餘 80% × 50%

關鍵動態視圖

• V$PROCESS：新增 PGA_USED_MEM, PGA_ALLOC_MEM, PGA_MAX_MEM
• V$SYSSTAT：新增 workarea optimal/one-pass/multi-pass 統計
• V$PGASTAT：實例級 PGA 使用摘要（自動目標、全局上限、高水位等）
• V$SQL_PLAN、V$WORKAREA、V$WORKAREA_ACTIVE

監控腳本示例

Workarea 執行分佈

SELECT name profile, cnt,
       DECODE(total,0,0,ROUND(cnt*100/total)) percentage
FROM (
  SELECT name, value cnt, SUM(value) OVER () total
  FROM v$sysstat
  WHERE name LIKE 'workarea exec%'
);

輸出解讀：multi-pass > 0 → 增大 PGA；optimal = 100% → 可嘗試減小。

PGA 統計快照

COLUMN name FORMAT A30
COLUMN value FORMAT 999,999,999
SELECT name, value FROM v$pgastat;

重點關注：aggregate PGA auto target、global memory bound（<1M 需增大）、estimated PGA memory for one-pass。

自動化切換腳本（UNIX Cron）

# dss_config.ksh - 每晚 18:00 切換至 DSS 模式
sqlplus -s /nolog <