Oracle 記憶體結構深度解析：PGA 管理機制與最佳實踐

PGA 概述：程序全域區核心概念

PGA（Program Global Area，程序全域區）是 Oracle 伺服器進程專用的私有記憶體區域，與共享的 SGA 形成鮮明對比。每個專用伺服器進程擁有獨立的 PGA，進程建立時分配、終止時回收。

PGA 內部結構組成

在專用伺服器連線模式下，PGA 包含 UGA（User Global Area，用戶全域區）與作業區：

PGA = UGA + 排序區 + 雜湊區 + 位圖合併區

• UGA：儲存會話狀態、游標資訊、套件變數等會話級資料
• 排序區：ORDER BY、GROUP BY、索引建立等排序操作使用
• 雜湊區：雜湊連接、雜湊聚合等操作使用
• 位圖合併區：位圖索引合併操作使用

PGA 兩大管理模式

1. 手動 PGA 記憶體管理（Manual）

由 DBA 明確指定各作業區大小參數，所有連線使用相同固定值：

• SORT_AREA_SIZE：排序操作最大記憶體
• SORT_AREA_RETAINED_SIZE：排序完成後保留在記憶體的資料量
• HASH_AREA_SIZE：雜湊操作記憶體

關鍵行為：

• 若排序資料超過 SORT_AREA_SIZE，Oracle 會分批排序，中間結果寫入臨時表空間
• 單一查詢可能包含多個排序/雜湊操作，每個操作獨立分配記憶體
• 記憶體按需分配，設定上限不代表實際全數佔用

2. 自動 PGA 記憶體管理（Auto）（9iR2 起預設）

啟用條件：

WORKAREA_SIZE_POLICY = AUTO
PGA_AGGREGATE_TARGET > 0

核心機制：

• PGA_AGGREGATE_TARGET 為「目標值」而非硬性上限，Oracle 動態調整各進程分配
• 連線少時：單進程可獲得更多記憶體（甚至超過目標值）
• 連線多時：自動壓縮單進程分配，總量控制在目標值附近
• 串行查詢單一作業區上限：PGA_AGGREGATE_TARGET 的 5%
• 並行查詢總上限：PGA_AGGREGATE_TARGET 的 30%

手動 vs 自動管理：場景化選擇指南

混合策略最佳實踐

• 白天尖峰期：啟用自動管理，應對多變負載
• 夜間維護窗口：在特定 Session 切換手動模式，分配大量 SORT_AREA_SIZE / HASH_AREA_SIZE（可達物理記憶體 50%~80%），加速大表排序、建索引、資料搬移等批次作業

實操指令速查

系統層級（需重啟或 SPFILE 生效）

ALTER SYSTEM SET WORKAREA_SIZE_POLICY = AUTO;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 100000000;
ALTER SYSTEM SET SORT_AREA_SIZE = 65536 SCOPE = SPFILE;
ALTER SYSTEM SET HASH_AREA_SIZE = 65536 SCOPE = SPFILE;

會話層級（即時生效，適合維護窗口切換）

ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
ALTER SESSION SET SORT_AREA_SIZE = 104857600;  -- 100MB
ALTER SESSION SET HASH_AREA_SIZE = 104857600;

實測驗證：自動管理下的排序效能

-- 環境：PGA_AGGREGATE_TARGET = 100MB，單查詢上限 5MB
CREATE TABLE sorttable AS SELECT * FROM all_objects;
INSERT INTO sorttable SELECT * FROM sorttable;  -- 重複擴充資料量
SET TIMING ON;
SET AUTOTRACE TRACEONLY;
SELECT * FROM sorttable ORDER BY object_id;
-- 觀察執行時間與臨時表空間使用情況

總結

自動 PGA 管理是現代 Oracle 環境的標準配置，能在多數場景下自動平衡記憶體使用。掌握會話級手動切換技巧，可為特定高強度維護作業爭取最大效能紅利。建議配合 V$PGASTAT、V$SQL_WORKAREA、V$SQL_WORKAREA_ACTIVE 等視圖持續監控調優。