Oracle 命中率查詢與 SGA 參數調優速查表

三大核心命中率查詢

-- 1. Buffer Cache Hit Ratio (目標 > 0.90)
SELECT 1 - (phy.value / (db.value + con.value))
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';

-- 2. Library Cache Hit Ratio (目標 > 0.99)
SELECT SUM(pins - reloads) / SUM(pins) FROM v$librarycache;

-- 3. Dictionary Cache Hit Ratio (目標 > 0.85)
SELECT SUM(gets - getmisses - usage - fixed) / SUM(gets) FROM v$rowcache;

回滾段爭用與縮減監控

-- 爭用：WAITS/GETS < 1%
SELECT SUM(gets), SUM(waits), SUM(waits)/SUM(gets) FROM v$rollstat;

-- 縮減次數
SELECT rn.name, rs.shrinks
FROM v$rollstat rs, v$rollname rn
WHERE rs.usn = rn.usn;

SGA 參數調優參考配置 (專機、64-bit、Archivelog)

關鍵參數建議值

• LOG_BUFFER：128K ~ 1M，勿過大。
• LARGE_POOL_SIZE：10M ~ 50M (RMAN/Parallel Query 使用)。
• JAVA_POOL_SIZE：不跑 Java 給 30M 即可。
• SHARED_POOL_SIZE：不宜超過 300M (12c 後可更大)，避免管理開銷抵銷效益。
• 剩餘記憶體全分給 DB_BUFFER。

總量原則

OS RAM + SGA + Sessions × (SORT_AREA_SIZE + HASH_AREA_SIZE + 2M) < 實體 RAM