您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 9i 動態記憶體管理深度解析：SGA/PGA 自動調整、OLTP vs DSS 配置策略

編輯：Oracle 資料庫基礎

前言：靜態到動態的範式轉移

Oracle 9i 前，SGA 大小僅能在啟動時透過 init.ora 靜態設定，調整需重啟實例。9i 引入 動態 SGA 與 自動 PGA 管理，實現運行期記憶體彈性配置，為 7x24 系統帶來革命性彈性。

一、動態 SGA 核心機制

關鍵參數

參數	說明	動態性	範圍
`SGA_MAX_SIZE`	SGA 總上限（啟動後不可變）	靜態	實例級
`DB_CACHE_SIZE`	預設 Buffer Cache (標準區塊大小)	動態	實例級
`SHARED_POOL_SIZE`	Shared Pool 大小	動態	實例級
`LARGE_POOL_SIZE`	Large Pool 大小	動態	實例級
`JAVA_POOL_SIZE`	Java Pool 大小	動態	實例級
`STREAMS_POOL_SIZE`	Streams Pool (10g+)	動態	實例級

動態調整實戰

-- 查看目前 SGA 組件大小
SELECT component, current_size/1024/1024 AS size_mb 
FROM v$sga_dynamic_components;

-- 動態調整 Buffer Cache (+200M)
ALTER SYSTEM SET db_cache_size = 800M;

-- 動態調整 Shared Pool (-100M)
ALTER SYSTEM SET shared_pool_size = 300M;

-- 查看調整歷史
SELECT * FROM v$sga_resize_ops ORDER BY start_time DESC;

Granule 顆粒度概念

SGA 記憶體以 Granule (顆粒) 為單位分配/回收
Granule 大小：SGA < 1GB → 4MB；SGA ≥ 1GB → 16MB
動態調整實際以 Granule 為最小單位

二、自動 PGA 管理：告別 SORT_AREA_SIZE

9i 前 vs 9i+ 對比

啟用自動 PGA

ALTER SYSTEM SET pga_aggregate_target = 1G SCOPE=BOTH;
ALTER SYSTEM SET workarea_size_policy = AUTO;  -- 預設即為 AUTO

-- 查看 PGA 使用統計
SELECT * FROM v$pgastat;
SELECT name, value FROM v$sysstat WHERE name LIKE '%pga%';

三、OLTP vs DSS 記憶體配置策略

不同負載類型對記憶體需求截然不同：

模式	參數	特點
手動模式 (9i 前)	SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE	所有 Session 共用固定大小，浪費或不足
自動模式 (9i+ 推薦)	`PGA_AGGREGATE_TARGET`, `WORKAREA_SIZE_POLICY=AUTO`	Oracle 自動按負載分配，最優化排序/雜湊效能

維度	OLTP (線上交易)	DSS/倉庫 (分析查詢)
特徵	高並發、小交易、隨機存取	低並發、大查詢、全表掃描、大量排序/雜湊
SGA 需求	大：Buffer Cache 熱點資料、Shared Pool SQL 重用	中等：Buffer Cache 較小，Large Pool 供平行查詢
PGA 需求	小：少量排序/雜湊	大：大量排序、Hash Join、Bitmap 操作
建議比例	SGA : PGA ≈ 70% : 30%	SGA : PGA ≈ 40% : 60%

動態切換腳本範例（白天 OLTP / 夜間 DSS）

-- 白天 08:00 切換至 OLTP 模式
BEGIN
  -- 增加 SGA，減少 PGA
  EXECUTE IMMEDIATE 'ALTER SYSTEM SET sga_target = 3G SCOPE=MEMORY';
  EXECUTE IMMEDIATE 'ALTER SYSTEM SET pga_aggregate_target = 1G SCOPE=MEMORY';
END;
/

-- 夜間 22:00 切換至 DSS 模式
BEGIN
  -- 減少 SGA，增加 PGA
  EXECUTE IMMEDIATE 'ALTER SYSTEM SET sga_target = 2G SCOPE=MEMORY';
  EXECUTE IMMEDIATE 'ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=MEMORY';
END;
/

注意： 9i 需分別設定各 SGA 組件；10g+ 引入 SGA_TARGET 統一管理，11g+ 更有 MEMORY_TARGET 統管 SGA+PGA。

四、UNIX 層面記憶體管理實作

Oracle 透過 shmget()/shmat() (System V Shared Memory) 管理 SGA
動態擴展本質：shmctl() 增加共享記憶體段、shmat() 映射到進程位址空間
PGA 使用 malloc()/mmap() 進程私有堆疊
關鍵 OS 參數：shmmax (單一共享記憶體段上限)、shmall (總共享頁數)、memlock (鎖定記憶體防換頁)

五、監控與診斷視圖

-- SGA 動態元件
SELECT component, current_size/1024/1024 AS mb, last_oper_type, last_oper_time
FROM v$sga_dynamic_components;

-- SGA 調整操作歷史
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;

-- PGA 建議值 (10g+)
SELECT * FROM v$pga_target_advice;

-- 記憶體總覽 (11g+)
SELECT * FROM v$memory_dynamic_components;