Oracle 效能調優總結：SGA 結構、記憶體運作機制與關鍵命中率

SGA 核心組件

記憶體運作四階段

1. Shared Pool 檢核：有無相同 SQL (Library Cache Hit/Miss) → 配置 Shared/Private SQL Area。
2. Dictionary Cache 檢核：有無物件定義 (Row Cache Miss 觸發磁碟讀入)。
3. Buffer Cache 檢核：有無目標區塊 (Hit) → LRU 掃描找 Free Buffer (Miss 觸發 DBWn 寫 Dirty Buffer)。
4. Redo 分配：DML 操作分配 Redo Log Buffer，滿則觸發 LGWR 寫入。

常用命中率查詢腳本

-- Buffer Cache Hit Ratio
SELECT 1 - (phy.value / (db.value + con.value)) AS hit_ratio
FROM v$sysstat phy, v$sysstat db, v$sysstat con
WHERE phy.name = 'physical reads'
  AND db.name = 'db block gets'
  AND con.name = 'consistent gets';

-- Library Cache Hit Ratio
SELECT SUM(pins - reloads) / SUM(pins) FROM v$librarycache;

-- Dictionary Cache Hit Ratio
SELECT SUM(gets - getmisses - usage - fixed) / SUM(gets) FROM v$rowcache;

調優方向

• Buffer Cache Hit 低 → 增加 DB_BLOCK_BUFFERS 或啟用多塊讀 (DB_FILE_MULTIBLOCK_READ_COUNT)。
• Library Cache Miss 多 → 增加 SHARED_POOL_SIZE、使用綁定變數、釘住熱門 PL/SQL (DBMS_SHARED_POOL.KEEP)。
• Dictionary Cache Miss 多 → 增加 Shared Pool、收集統計資訊減少動態採樣。
• Redo Log Buffer 等待 → 增加 LOG_BUFFER、檢查 LGWR I/O 延遲。