Oracle 9i 效能調優筆記 (一)：Buffer Cache 命中率測量與優化策略

Buffer Cache 運作機制回顧

• LRU List：MRU (Most Recently Used) 端 ↔ LRU (Least Recently Used) 端。全表掃描 (FTS) 塊直接放入 LRU 端，避免汙染熱點資料。
• Buffer 狀態：Free / Pinned / Clean / Dirty。
• Dirty List / Write List：臟塊鏈結，由 DBWn 背景進程批次寫入資料檔。

核心指標：Buffer Cache Hit Ratio

SELECT 1 - ( (physical.value - direct.value - lobs.value) / logical.value ) AS "Buffer Cache Hit Ratio"
FROM   v$sysstat physical,
       v$sysstat direct,
       v$sysstat lobs,
       v$sysstat logical
WHERE  physical.name = 'physical reads'
AND    direct.name   = 'physical reads direct'
AND    lobs.name     = 'physical reads direct (lob)'
AND    logical.name  = 'session logical reads';

**目標**：> 90% (OLTP 建議 > 95%)。若偏低，優先檢查 SQL 邏輯而非盲目加記憶體。

多維度監控視角

四大優化策略

1. 調大 Buffer Cache — 動態參數

-- 9i: DB_CACHE_SIZE (取代 DB_BLOCK_BUFFERS)
ALTER SYSTEM SET DB_CACHE_SIZE = 500M SCOPE=BOTH;

-- 10g+ 自動記憶體管理 (AMM/ASMM) 更彈性
ALTER SYSTEM SET SGA_TARGET = 2G SCOPE=BOTH;

2. Buffer Cache Advisory — 科學定尺寸

-- 開啟建議引擎
ALTER SYSTEM SET DB_CACHE_ADVICE = ON SCOPE=BOTH;

-- 查詢建議
SELECT size_for_estimate, buffers_for_estimate,
       estd_physical_read_factor, estd_physical_reads
FROM   v$db_cache_advice
ORDER  BY size_for_estimate;

觀察 ESTD_PHYSICAL_READ_FACTOR 下降趨勢，尋找「邊際效益遞減點」。

3. 多重 Buffer Pool 隔離熱/冷資料

-- 建表指定 Pool
CREATE TABLE hot_lookup (...) STORAGE (BUFFER_POOL KEEP);
CREATE TABLE big_archive (...) STORAGE (BUFFER_POOL RECYCLE);
ALTER TABLE existing_table STORAGE (BUFFER_POOL KEEP);

4. 小表快取 — 避免重複 FTS

ALTER TABLE small_ref_table CACHE;  -- 9i/10g
-- 或放入 KEEP Pool (更精確控制)

Large Pool 與 Java Pool 微調

• Large Pool：Shared Server (MTS)、RMAN、Parallel Query、DBWR Slave。LARGE_POOL_SIZE 預設 8M，監控 V$SGASTAT WHERE POOL='large pool' 免費空間。
• Java Pool：Java Stored Procedure、EJB。大型 Java 應用建議 > 50M。JAVA_POOL_SIZE 監控同樣查 V$SGASTAT。

結語

Buffer Cache 優化**不是單純加記憶體**，而是：**測量命中率 → 識別非命中根因 (熱點表、大掃描、排序溢寫) → 針對性策略 (KEEP/RECYCLE/CACHE/Advisory) → 持續監控**。9i 奠基了動態調整與多 Pool 架構，現代版本 (19c/21c) 更引入 In-Memory Column Store，將熱點資料以行/欄雙格式驻留，徹底改寫存取路徑。