Oracle Buffer Cache 調優實戰：從 V$DB_CACHE_ADVICE 到多 Buffer Pool 設計

一、兩大評估方法

1. V$DB_CACHE_ADVICE (需啟用 DB_CACHE_ADVICE=ON)

SELECT size_for_estimate,
       buffers_for_estimate,
       estd_physical_read_factor,
       estd_physical_reads
  FROM v$db_cache_advice
 WHERE name = 'DEFAULT'
   AND block_size = (SELECT value FROM v$parameter WHERE name = 'db_block_size')
   AND advice_status = 'ON';

• SIZE_FACTOR = 1 代表目前大小；觀察擴大時物理讀下降幅度決定是否調大。

2. Buffer Cache Hit Ratio

SELECT 1 - ((physical.value - direct.value - lobs.value) / logical.value) "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';

• 誤區：高命中率 ≠ Cache 足夠。大表全掃描屬物理讀會人為降低比率；重複掃描小表則虛高比率。
• 實務建議：檢查 BUFFER_GETS 高的 SQL、Cache 熱點資料，而非單看比率。

二、非標準區塊大小設定

DB_CACHE_SIZE 僅對應預設 DB_BLOCK_SIZE。若有 2K/4K/16K/32K 表空間，需額外設定 DB_2K_CACHE_SIZE、DB_16K_CACHE_SIZE 等參數。

三、Multiple Buffer Pools 設計

• 三者皆採 LRU 演算法。
• 查詢 Pool 資訊：SELECT * FROM v$buffer_pool;
• 將物件指定 Pool：ALTER TABLE t STORAGE (BUFFER_POOL KEEP);，可在 DBA_TABLES.BUFFER_POOL 驗證。

四、大區段存取策略

1. 若為索引，先檢查選擇度，不佳則優化 SQL。
2. 已優化仍為大掃描 → 放入 RECYCLE Pool。
3. 小熱表/索引 → 放入 KEEP Pool，降低 Cache Miss。

五、KEEP Pool 容量規劃

• 依欲保留區段大小加總：查 V$BH 或 DBA_TABLES.BLOCKS - EMPTY_BLOCKS 估算 used blocks。
• 設定 DB_KEEP_CACHE_SIZE 獨立於 Default Pool。
• 注意：NOCACHE 屬性對 KEEP Pool 無效。