Oracle 緩衝區快取內部機制與效能調優：從 Midpoint Insertion 到全庫快取實戰

理解 Database Buffer Cache 內部運作是 SQL 調優與實例調優的基石。本文深入解析 Oracle 8i 引入的 Midpoint Insertion Strategy、Touch Count 熱度追蹤、Hot/Cold 區域劃分、隱藏參數、X$BH 熱塊識別，以及 64 位元架構下全庫快取的可行性分析。

Buffer Cache 管理演進

Touch Count (TCH) 機制

• 每個 Buffer Header 維護 TCH 計數器。
• Session 存取塊時 TCH++（受 _db_aging_touch_time 節流，預設 3 秒內重複存取不重複計數）。
• TCH > _db_aging_hot_criteria（預設 10）時，塊從 Cold 區移動到 Hot 區（MRU 端）。
• Hot 區佔比由隱藏參數控制：
  _db_percent_hot_default (預設 50%)
  _db_percent_hot_keep
_db_percent_hot_recycle

警告：隱藏參數僅供 Oracle Support 指導下調整，錯誤設定會導致 Cache 效率劇降甚至實例不穩定。

識別熱塊：X$BH 查詢

COL object FORMAT A30
COL file# FORMAT 9999
COL block# FORMAT 999999
COL touches FORMAT 999999

SELECT 
  o.object_name AS object,
  bh.dbarfil AS file#,
  bh.dbablk AS block#,
  bh.tch AS touches
FROM x$bh bh
JOIN dba_objects o ON bh.obj = o.object_id
WHERE bh.tch > 10
ORDER BY bh.tch DESC;

結果解讀：TCH 高的物件為熱門存取對象，候選移入 KEEP Pool（DB_KEEP_CACHE_SIZE）長期駐留，避免被大掃描沖刷。

多 Buffer Pool 架構

-- 將熱門表指派到 KEEP Pool
ALTER TABLE hot_table STORAGE (BUFFER_POOL KEEP);
ALTER INDEX hot_idx REBUILD STORAGE (BUFFER_POOL KEEP);

全庫快取可行性分析

64 位元定址 + 記憶體價格下降，使得 完全快取中小型資料庫 成為現實：

• 磁碟存取：毫秒級
• 記憶體存取：納秒級
• 速度差：~14,000 倍

快取規劃步驟

1. 計算已分配區塊數：

   SELECT SUM(blocks) FROM dba_data_files;  -- 配置總塊數
   SELECT SUM(blocks) FROM dba_extents;     -- 實際使用塊數
   

2. 換算記憶體需求：已用塊數 × DB_BLOCK_SIZE（含索引、系統表空間、Undo、Temp）。
3. 設定 DB_CACHE_SIZE ≥ 實際需求 + 20% 預留。
4. 啟動階段預熱腳本：

   -- 預熱腳本範例
   BEGIN
     FOR r IN (SELECT owner, table_name FROM dba_tables WHERE owner NOT IN ('SYS','SYSTEM')) LOOP
       EXECUTE IMMEDIATE 'SELECT /*+ FULL(t) */ COUNT(*) FROM '||r.owner||'.'||r.table_name||' t';
     END LOOP;
   END;
   /
   

5. 驗證：SELECT * FROM v$db_cache_advice、Buffer Cache Hit Ratio > 99%。

調優檢查清單

1. Buffer Cache Hit Ratio 持續 < 90% → 擴大 DB_CACHE_SIZE 或啟用 KEEP/RECYCLE 分離。
2. PHYSICAL READS 高、DB FILE SCATTERED READ 等待多 → 檢查大掃描是否可加索引/分區裁剪/並行查詢。
3. BUFFER BUSY WAITS 多 → 熱塊競爭，考慮 PCTFREE、INITRANS、Reverse Key Index、Hash Partition。
4. FREE BUFFER WAITS 多 → DBWR 寫入跟不上，增加 DB_WRITER_PROCESSES、檢查儲存 IOPS、啟用 Async I/O。

Buffer Cache 不是越大越好：超過作業系統可用記憶體會觸發 Swapping，導致災難性效能崩塌。
黃金法則：盡可能大、但絕不 Swap。定期跑 DB_CACHE_ADVICE 找最佳平衡點。