Oracle PGA (Program Global Area) 核心機制深度解析：排序、會話、堆疊、游標區

PGA 是伺服器進程專用的私有記憶體區域，直接決定排序效能、並發承載與記憶體壓力。本文深入剖析 PGA 四大子區 (排序區、會話區、堆疊區、游標區) 運作原理、參數調校與監控診斷實戰。

PGA vs SGA 核心差異

四大子區詳細剖析

1. 排序區 - 效能關鍵

• 功能：ORDER BY、GROUP BY、DISTINCT、UNION/INTERSECT/MINUS、索引建立、合併連線等操作的中間結果暫存與運算區。
• 運作模式：
  • 記憶體內排序 (最佳)：資料完全置於排序區，CPU 直接運算，微秒級延遲。
  • 磁碟排序 (降級)：排序區不足，溢寫至暫存表空間 (TEMP)，產生大量 I/O，效能下降 10-100 倍。
• 關鍵參數：
  • SORT_AREA_SIZE (9i 手動模式)：每個排序操作最大記憶體。
  • PGA_AGGREGATE_TARGET (10g+ 自動模式)：總 PGA 預算，Oracle 自動分配 _SMM_MAX_SIZE (單次排序上限，通常為 PGA 目標的 5-10%)。
  • WORKAREA_SIZE_POLICY = AUTO (預設) 啟用自動管理。
• 監控指標：

  -- 查詢磁碟排序次數 (應趨近 0)
  SELECT name, value FROM v$sysstat WHERE name LIKE '%sort%';
  
  -- 當前工作區使用情況
  SELECT * FROM v$sql_workarea_active;

2. 會話區 - 安全與狀態中樞

• 儲存內容：使用者權限清單 (角色、系統/物件權限)、NLS 參數、資源消耗統計、應用程式上下文 (Client Identifier, Module, Action)。
• 安全意義：權限檢查在記憶體中完成，避免每次查詢讀取字典表 (SYS.USER$, SYS.OBJ$ 等)，極大降低延遲。
• 生命週期：登入建立，登出/逾時自動釋放。DBA 通常無需干預。

3. 堆疊區 - 變數與執行上下文

• 綁定變數：SQL 重用關鍵，SELECT * FROM emp WHERE deptno = :b1 中的 :b1 值儲存於此。
• PL/SQL 執行上下文：區域變數、呼叫堆疊、例外處理框架。
• 動態分配：隨程式複雜度增長，自動向作業系統申請記憶體 (受 PGA_AGGREGATE_LIMIT 限制)。

4. 游標區 - SQL 執行狀態機

• 開啟/關閉成本：每次 OPEN CURSOR 分配結構、解析 (若未共用)、鎖定庫存單元；CLOSE 釋放。頻繁開關 = CPU/閂爭用。
• 關鍵參數：OPEN_CURSORS (預設 300) 限制 Session 同時開啟游標數。超過拋 ORA-01000。
• 最佳實踐：
  • 應用端使用 **連線池 + PreparedStatement 重用**，避免重複剖析與開關。
  • PL/SQL 隱性游標 (FOR 迴圈) 自動管理，優於顯性 OPEN/FETCH/CLOSE。
  • 監控 v$open_cursor 找出洩漏會話。

自動 PGA 管理調校實戰 (10g+)

1. 設定目標：ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 2G SCOPE=BOTH; (視實體記憶體、SGA、OS 需求留 20-30% 給 PGA)。
2. 設定硬上限 (12c+)：ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 4G SCOPE=BOTH; 防止失控進程耗盡記憶體導致 OOM Killer 殺掉 Oracle 進程。
3. 觀察建議：SELECT * FROM v$pga_target_advice; —— 顯示不同目標值下的預估磁碟排序量與額外記憶體需求。
4. 調整策略：
   • 磁碟排序量 > 0 且有記憶體餘裕 → 增加 Target。
   • OS 可用記憶體 < 10% → 降低 Target 或增加實體記憶體。
   • 特定大排序作業 (建立索引、大表連線) → Session 層級 ALTER SESSION SET SORT_AREA_SIZE = 100M; (需 WORKAREA_SIZE_POLICY=MANUAL 臨時切換)。

常見問題診斷對照表

總結：PGA 管理黃金法則

1. 自動為主：PGA_AGGREGATE_TARGET + WORKAREA_SIZE_POLICY=AUTO。
2. 設定上限：12c+ 必設 PGA_AGGREGATE_LIMIT 防禦記憶體耗盡。
3. 監控磁碟排序：零容忍核心業務磁碟排序，納入日常巡檢儀表板。
4. 游標重用：應用架構層面強制綁定變數與連線池，根治剖析風暴與游標洩漏。
5. 大作業隔離：建立索引、資料遷移等大排序作業，專用 Session 臨時調大排序區或使用平行度分散壓力。