Oracle 進程架構核心概念深度解析：SGA、PGA、背景進程與 SQL 執行流程

一、記憶體架構雙核心：SGA 與 PGA

SGA (System Global Area) — 實例共享記憶體

• 實例啟動時分配，核心組件：
  1. Database Buffer Cache：儲存資料檔區塊 (預設 8KB，支援 2K/4K/8K/16K/32K)，LRU 淘汰，DB_BLOCK_SIZE 指定。含三大子緩衝區：DB_CACHE_SIZE (預設)、DB_KEEP_CACHE_SIZE (熱點釘住)、DB_RECYCLE_CACHE_SIZE (冷數據快速淘汰)。可啟用 DB_CACHE_ADVICE 由系統建議大小。
  2. Redo Log Buffer：重做日誌條目順序寫入，LOG_BUFFER 控制大小，ALTER SYSTEM SET LOG_BUFFER=60M 動態調整。ARCHIVE LOG LIST 查看歸檔模式。
  3. Shared Pool：Library Cache (SQL/PLAN)、Data Dictionary Cache、Result Cache 等。
  4. Large Pool：共享伺服器 UGA、RMAN 備份緩衝、並行查詢訊息緩衝 (可選，LARGE_POOL_SIZE)。
  5. Java Pool：Java 代碼/會話狀態 (啟用 Java 時使用)。
  6. Streams Pool：Streams/GoldenGate 複製進程緩衝。

PGA (Program Global Area) — 進程私有記憶體

• Server Process 啟動時分配，包含：Sort Area (排序/雜湊/位圖合併)、Session Info、Stack Space。
• 專用連線：PGA 獨享；共享連線：UGA 在 SGA Large Pool，僅 Sort Area 在 PGA。
• PGA_AGGREGATE_TARGET + WORKAREA_SIZE_POLICY=AUTO 自動管理 (10g+ 推薦)。

二、進程結構三層級

三、九大核心背景進程詳細職責

四、SQL 執行關鍵流程

1. 連線建立：User Process → Listener → Server Process (專用/共享)。
2. 解析：語法/語義檢查、權限驗證、最佳化 (CBO/RBO) 生成執行計劃 → Library Cache。
3. 執行：
   • Query：Buffer Cache 讀取 → 必要時磁碟 I/O → 結果集返回。
   • DML：產生 Redo 寫入 Log Buffer、修改 Buffer Cache 生成 Undo、鎖定資源。
4. Commit：LGWR 強制寫入 Redo Log (含 Commit 記錄、SCN)，釋放鎖，標記事務完成。

非所有背景進程參與每條 SQL：僅按需喚醒，部分服務效能 (DBWn 延寫)、部分保障恢復 (LGWR/ARCn)、部分維護健康 (SMON/PMON)。

五、實例 vs 資料庫

• Oracle Instance：SGA + 背景進程 = 活躍的記憶體與進程結構，啟動後才能存取資料。
• Oracle Database：物理檔案集合 (資料檔、控制檔、Redo Log、參數檔、密碼檔) = 持久化儲存。

實例可獨立於資料庫啟動 (NOMOUNT)；一資料庫可被多實例掛載 (RAC)。