Oracle 10g 記憶體架構深度解析：系統全局區 (SGA) 三大核心組件詳解

SGA 概觀

系統全局區 (System Global Area, SGA) 是一組共享記憶體結構，包含單一資料庫實例的資料與控制資訊。多用戶連線同一實例時共享 SGA，故又稱「共享全局區」。SGA 依功能分為三大核心組件：

1. 資料緩衝區 (Database Buffer Cache)
2. 日誌緩衝區 (Redo Log Buffer)
3. 共享池 (Shared Pool)

一、資料緩衝區

功能與運作機制

• 暫存從磁碟讀入的資料塊，供所有用戶共享
• 修改/插入的資料先寫入緩衝區，標記為「髒塊」，由 DBWR 背景程序批次寫回資料檔
• 採用 LRU (Least Recently Used) 算法 管理：優先保留近期存取塊，淘汰舊塊

10g 多塊大小架構與參數

10g 引入多區塊大小，不同表空間可使用不同區塊大小優化 I/O：

注意： 標準區塊大小不可同時設定對應的 DB_nK_CACHE_SIZE。例如 DB_BLOCK_SIZE=2048 時，不得設定 DB_2K_CACHE_SIZE，標準區塊必須用 DB_CACHE_SIZE。

動態調整與參數檔範例

-- 線上調整
ALTER SYSTEM SET DB_2K_CACHE_SIZE = 10M;

-- 參數檔寫法
DB_BLOCK_SIZE=4096
DB_CACHE_SIZE=20971520
DB_2K_CACHE_SIZE=8M
DB_8K_CACHE_SIZE=4M

緩衝區命中率計算

方法一（v$sysstat 經典寫法）：

SELECT A.VALUE + B.VALUE "LOGICAL_READS",
       C.VALUE "PHYS_READS",
       ROUND(100 * ((A.VALUE + B.VALUE) - C.VALUE) / (A.VALUE + B.VALUE)) "BUFFER_HIT_RATIO"
FROM V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C
WHERE A.STATISTIC# = 38 AND B.STATISTIC# = 39 AND C.STATISTIC# = 40;

方法二（名稱查詢法）：

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('SESSION LOGICAL READS', 'PHYSICAL READS', 'PHYSICAL READS DIRECT', 'PHYSICAL READS DIRECT (LOB)');

Hit Ratio = 1 - ((PHYSICAL READS - DIRECT READS - LOB DIRECT READS) / SESSION LOGICAL READS)。建議目標 > 90%。

二、日誌緩衝區

功能

• 暫存資料庫修改產生的 Redo 記錄
• LGWR 背景程序將滿/定期/Commit 觸發時寫入線上重做日誌檔組
• ARCH 歸檔程序再轉存至歸檔介質

大小參數與查詢

• 參數：LOG_BUFFER (Bytes)
• 查詢三法：參數檔、SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME LIKE '%BUFFER'、SHOW PARAMETER LOG_BUFFER

Latch 失敗率監控（應 < 1%）

SELECT NAME, GETS, MISSES, IMMEDIATE_GETS, IMMEDIATE_MISSES,
       DECODE(GETS, 0, 0, MISSES/GETS*100) RATIO1,
       DECODE(IMMEDIATE_GETS+IMMEDIATE_MISSES, 0, 0,
              IMMEDIATE_MISSES/(IMMEDIATE_GETS+IMMEDIATE_MISSES)*100) RATIO2
FROM V$LATCH
WHERE NAME IN ('REDO ALLOCATION', 'REDO COPY');

使用者等待日誌緩衝區次數

SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME = 'REDO BUFFER ALLOCATION RETRIES';

三、共享池

詳見下一篇「Oracle 10g 記憶體架構：共享池詳解」。

SGA 總大小查詢

SHOW SGA
-- 或
SELECT * FROM V$SGA;

SGA 空閒空間比例

COL OBJECT_NAME FORMAT A20
COL "空閒空間百分比(%)" FORMAT 90.99
SELECT NAME,
       SGASIZE/1024/1024 "ALLOCATED(M)",
       BYTES/1024 "空閒空間(K)",
       ROUND(BYTES/SGASIZE*100, 2) "空閒空間百分比(%)"
FROM (SELECT SUM(BYTES) SGASIZE FROM SYS.V_$SGASTAT) S, SYS.V_$SGASTAT F
WHERE F.NAME = 'FREE MEMORY';

總結

SGA 三大組件各司其職：資料緩衝區決定讀取效能（命中率 >90%）、日誌緩衝區影響寫入延遲（Latch 失敗率 <1%）、共享池關鍵於 SQL/PLSQL 執行效率。10g 多區塊大小與動態參數調整提供更精細的記憶體管理能力。