Oracle 記憶體架構深度解析：SGA 七大元件、參數控制與調校策略

SGA (System Global Area) 是 Oracle 執行個體共享記憶體核心，由七大元件組成。本文深度剖析各元件功能、關鍵參數控制關係、自動/手動管理模式切換，並給出生產環境調校建議。

一、 SGA 七大元件功能圖譜

二、 記憶體管理模式演進

關鍵參數關係

• SGA_MAX_SIZE：硬上限，修改需重啟
• SGA_TARGET：ASMM 目標值，動態可調，≤ SGA_MAX_SIZE
• 若 SGA_TARGET = 0 → 手動模式，各 *_SIZE 生效
• 若 SGA_TARGET > 0 → 自動模式，非零 *_SIZE 視為**最小值保證**

三、 Buffer Cache 三池架構與多區塊大小

1. 三池分工

• Default Pool：所有物件預設緩存區
• Keep Pool：小型高頻存取表（代碼表、參考表），ALTER TABLE t STORAGE (BUFFER_POOL KEEP);
• Recycle Pool：大型低頻掃描表（歷史日誌、暫存表），避免汙染 Default Pool

三池記憶體**互不共享**，需分別設定大小。

2. 多區塊大小 (9i+)

-- 建立 16K 區塊表空間
CREATE TABLESPACE ts_16k DATAFILE '...' BLOCKSIZE 16K;
-- 必須同時建立對應快取
ALTER SYSTEM SET DB_16K_CACHE_SIZE = 100M;

支援 2K/4K/8K/16K/32K，標準區塊大小由 DB_BLOCK_SIZE 決定。

四、 Redo Log Buffer 與寫入觸發機制

• LGWR 觸發寫入條件（任一滿足）：
  ① 每 3 秒
  ② 緩衝區達 1MB 或 1/3 滿
  ③ 使用者 COMMIT
  ④ DBWR 寫入資料檔前 (Checkpoint)
• LOG_BUFFER 過小 → log buffer space 等待；過大浪費記憶體，通常 1~5 MB 足夠

五、 Shared Pool 內部結構與常見問題

六、 生產環境調校檢查清單

1. 啟用 ASMM (SGA_TARGET) 或 AMM (MEMORY_TARGET)，預留 SGA_MAX_SIZE 彈性空間
2. 監控 V$SGA_TARGET_ADVICE、V$MEMORY_TARGET_ADVICE 獲取 Oracle 建議值
3. Buffer Cache 命中率：
   SELECT 1 - (physical_reads / (db_block_gets + consistent_gets)) FROM v$sysstat; 目標 > 95%
4. Shared Pool 碎片化：
   SELECT * FROM v$sgastat WHERE pool='shared pool' AND name='free memory'; 免費記憶體 < 5MB 需關注
5. 大表全掃描 → 考慮 PARALLEL + LARGE_POOL 或 DB_16K_CACHE_SIZE
6. RMAN 備份慢 → 增加 LARGE_POOL_SIZE (至少 50MB 起)
7. Java/Streams 未啟用 → 將 JAVA_POOL_SIZE/STREAMS_POOL_SIZE 設 0 釋放記憶體

七、 12c/19c/21c 新特性

• In-Memory Column Store (IM column store)：獨立於 Buffer Cache，INMEMORY_SIZE 控制，行/混合/柱狀儲存
• Memoptimized Rowstore (19c)：高頻單鍵查詢專用快取，MEMOPTIMIZE_POOL_SIZE
• 自動索引 (19c/21c)：背景自動建立/刪除索引，間接影響 Buffer Cache/Shared Pool 壓力

SGA 調校核心在於「啟用自動管理建立基線 → 依 AWR/ASH 微調最小值保證 → 監控關鍵等待事件迭代」，而非一次設定終身不動。

1. 上一篇文章： Oracle 表連接四大類型實戰：等值、外部、不等值與自連接完整語法
2. 下一篇文章： Oracle Linux 靜默安裝自動化腳本：核心參數、用戶建立、環境變數一鍵配置

Oracle 資料庫基礎

PL/SQL 區塊、預存程序與套件基礎入門

前言 PL/SQL (Procedural Language

Oracle 到 SQL Server SQL 智能翻譯器架構設計：解釋器、組合、訪問者模式與有限狀態機實作

針對 Oracle PL/SQL 遷移至 T-SQL 的複雜

Navicat for Oracle 安裝與連線配置完整教學

工具簡介 Navicat for Oracle 是一套功能強

Oracle 監聽配置全攻略：listener.ora、tnsnames.ora、sqlnet.ora 三劍合璧

監聽器角色 獨立於數據庫進程的後台服務，監聽端口（默認 15