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

Oracle 記憶體架構核心：SGA 組成結構、查詢與固定/可變區域詳解

編輯：Oracle 資料庫基礎

System Global Area (SGA) 是 Oracle 實例啟動時分配的共享記憶體區域，所有背景程序與前景程序共同存取。本文系統解析 SGA 七大核心組件、固定區 vs 可變區劃分、`SHOW SGA` 實測輸出解讀、以及 12c+ 自動記憶體管理 (AMM/ASMM) 關鍵參數，建立完整記憶體架構心智模型。

SGA 七大核心組件

組件	功能	關鍵參數 (11g+)
Database Buffer Cache	緩存資料塊 (Table/Index/Undo...)，LRU 淘汰	DB_CACHE_SIZE, DB_nK_CACHE_SIZE
Redo Log Buffer	緩存 Redo Entry，LGWR 批次寫入線上日誌	LOG_BUFFER
Shared Pool	Library Cache (SQL/PLAN)、Data Dictionary Cache、Result Cache	SHARED_POOL_SIZE, RESULT_CACHE_MAX_SIZE
Large Pool	大記憶體分配：RMAN、共享伺服器 UGA、平行查詢訊息緩衝	LARGE_POOL_SIZE
Java Pool	Java Stored Procedure 類別/資料、JVM 狀態	JAVA_POOL_SIZE
Streams Pool	Streams/GoldenGate 佇列、適用 10g+	STREAMS_POOL_SIZE
Data Dictionary Cache	字典資訊緩存 (現多整合於 Shared Pool)	(隱含於 Shared Pool)

固定 SGA vs 可變 SGA

分類	包含內容	大小特性	代表參數
Fixed SGA	實例啟動資訊、鎖結構、列舉常數、背景程序陣列	極小 (<100KB)，啟動後固定不變	無可調參數
Variable SGA	Shared Pool、Large Pool、Java Pool、Streams Pool、Buffer Cache、Redo Log Buffer	依參數動態調整 (ASMM/AMM)	SGA_TARGET, SGA_MAX_SIZE, *_SIZE

實測：SHOW SGA 輸出解讀


SQL> SHOW SGA

Total System Global Area  612368384 bytes  -- 總 SGA ≈ 584 MB
Fixed Size                1250428 bytes    -- Fixed SGA ≈ 1.2 MB
Variable Size             192940932 bytes  -- Variable SGA ≈ 184 MB (Shared/Large/Java/Streams Pool)
Database Buffers          411041792 bytes  -- Buffer Cache ≈ 392 MB
Redo Buffers              7135232 bytes    -- Redo Log Buffer ≈ 6.8 MB

換算： 612,368,384 bytes ÷ 1024² ≈ 584 MB。

關鍵初始化參數 (11g/12c/19c)


-- 自動共享記憶體管理 (ASMM) - 推薦
SGA_TARGET = 2G          -- SGA 總目標值 (動態分配各組件)
SGA_MAX_SIZE = 4G        -- SGA 硬性上限 (靜態參數，需重啟)

-- 手動管理 (不設 SGA_TARGET 時)
DB_CACHE_SIZE = 1G
SHARED_POOL_SIZE = 512M
LARGE_POOL_SIZE = 64M
JAVA_POOL_SIZE = 32M
STREAMS_POOL_SIZE = 32M
LOG_BUFFER = 8M          -- 通常自動計算

-- 12c+ 自動記憶體管理 (AMM) - 單一參數
MEMORY_TARGET = 4G       -- SGA + PGA 總目標
MEMORY_MAX_TARGET = 8G   -- 硬性上限

SGA 動態調整與監控

V$SGA、V$SGAINFO、V$SGA_DYNAMIC_COMPONENTS：即時查看各組件大小、最後調整時間、操作類型 (GROW/SHRINK)。
V$SGA_RESIZE_OPS：歷史調整操作記錄 (最近 800 筆)。
V$BUFFER_POOL_STATISTICS：Buffer Cache 命中率、物理讀寫統計。
V$LIBRARYCACHE：Shared Pool 內 Library Cache 命中率、Reload/Pin 統計。

實務調優建議

優先啟用 ASMM (SGA_TARGET)： 讓 Oracle 自動平衡 Buffer Cache 與 Shared Pool，減少人工調整。
Buffer Cache 命中率 > 95%： 若偏低，檢查 `DB_CACHE_SIZE` 或 SQL 全表掃描過多。
Shared Pool 碎片化： 監控 `V$LIBRARYCACHE.RELOADS`，頻繁 Reload 考慮增大 `SHARED_POOL_SIZE` 或使用 `DBMS_SHARED_POOL.KEEP` 釘住熱門 Cursor。
Large Pool： 若使用共享伺服器 (Shared Server) 或 RMAN 多通道備份，必須設定 `LARGE_POOL_SIZE` 避免搶佔 Shared Pool。
Redo Log Buffer： 通常自動計算足夠，高寫入系統可適度調大 `LOG_BUFFER` (上限約幾十 MB)。
AMM (MEMORY_TARGET) 注意： 需 `/dev/shm` (Linux) 足夠大，且 PGA 與 SGA 爭奪記憶體可能導致不可預期的交換。

相關視圖速查表


-- SGA 總覽
SELECT * FROM V$SGA;
SELECT * FROM V$SGAINFO;

-- 各組件動態調整歷史
SELECT component, oper_type, oper_mode, parameter,
       initial_size/1024/1024 AS init_mb,
       final_size/1024/1024 AS final_mb,
       status, start_time, end_time
FROM   V$SGA_RESIZE_OPS
ORDER  BY start_time DESC;

-- Buffer Cache 命中率
SELECT 1 - (physical_reads / (db_block_gets + consistent_gets)) AS hit_ratio
FROM   V$BUFFER_POOL_STATISTICS
WHERE  name = 'DEFAULT';

掌握 SGA 七大組件職能、固定/可變區劃分、動態調整機制與關鍵參數，是 Oracle 效能調優、容量規劃、故障診斷的基石。