🍿 2.4 Oracle数据库SGA命中率检查

在 Oracle 11g 中，SGA（System Global Area）命中率是衡量数据库缓存效率的重要指标。常见的 SGA 命中率包括​数据缓冲区缓存命中率​、共享池命中率和​日志缓冲区命中率。以下是各项命中率的检查方法及解读。

1. 数据缓冲区缓存命中率

数据缓冲区缓存（Buffer Cache）用于缓存数据块，其命中率反映了从内存中直接获取数据的比例，应尽量接近 100%。

查询语句

SELECT
  ROUND((1 - (phy.value / (cur.value + con.value))) * 100, 2) AS "Buffer Cache Hit Ratio"
FROM
  v$sysstat cur,
  v$sysstat con,
  v$sysstat phy
WHERE
  cur.name = 'session logical reads'
  AND con.name = 'consistent gets'
  AND phy.name = 'physical reads';

解读

• 命中率 < 90% ​：可能存在大量物理 I/O，需检查 SQL 是否低效（如全表扫描），或考虑增加 db_cache_size。
• 命中率 > 95% ：通常表示缓存效率较高。

注意​：此公式将 consistent gets​ 和 db block gets​ 合并为逻辑读。另一种更精确的计算是 (1 - (physical reads) / (db block gets + consistent gets)) * 100。

2. 共享池命中率

共享池（Shared Pool）中的库缓存（Library Cache）用于缓存 SQL 和 PL/SQL 的执行计划。高命中率可减少硬解析开销。

查询语句

SELECT
  ROUND(SUM(pins - reloads) / SUM(pins) * 100, 2) AS "Library Cache Hit Ratio"
FROM
  v$librarycache;

解读

• 命中率 > 95% ：正常。
• 低于 90% ​：可能存在大量硬解析，检查是否未使用绑定变量，或共享池过小（调整 shared_pool_size）。

3. 字典缓存命中率

字典缓存（Row Cache）用于缓存数据字典信息，高命中率可减少递归 SQL 的物理 I/O。

查询语句

SELECT
  ROUND(SUM(gets - getmisses) / SUM(gets) * 100, 2) AS "Dictionary Cache Hit Ratio"
FROM
  v$rowcache;

解读

• 命中率 > 95% ：正常。
• 低于 85% ：可能共享池不足，或存在大量动态 SQL 访问数据字典（如频繁创建/删除对象）。

4. 日志缓冲区命中率

日志缓冲区（Log Buffer）用于缓存重做记录。如果重做记录在写入磁盘前被频繁重新访问，命中率会降低。

查询语句

SELECT
  ROUND((1 - (redo_entries / redo_buffer_alloc_retries)) * 100, 2) AS "Redo Log Buffer Hit Ratio"
FROM
  (SELECT VALUE redo_entries FROM v$sysstat WHERE name = 'redo entries'),
  (SELECT VALUE redo_buffer_alloc_retries FROM v$sysstat WHERE name = 'redo buffer allocation retries');

解读

• 命中率 > 98% ：通常正常。
• 命中率较低​：可能 log_buffer​ 过小，或事务提交过于频繁，建议增大 log_buffer 并优化提交策略。

5. 一键检查脚本（SGA 命中率总览）

SET LINES 200 PAGES 100
COL metric FORMAT A30
COL value FORMAT A15

PROMPT === SGA Hit Ratios ===

-- Buffer Cache Hit Ratio
SELECT 'Buffer Cache Hit Ratio' AS metric,
       ROUND((1 - (phy.value / (cur.value + con.value))) * 100, 2) || '%' AS value
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'session logical reads'
  AND con.name = 'consistent gets'
  AND phy.name = 'physical reads'
UNION ALL

-- Library Cache Hit Ratio
SELECT 'Library Cache Hit Ratio' AS metric,
       ROUND(SUM(pins - reloads) / SUM(pins) * 100, 2) || '%' AS value
FROM v$librarycache
UNION ALL

-- Dictionary Cache Hit Ratio
SELECT 'Dictionary Cache Hit Ratio' AS metric,
       ROUND(SUM(gets - getmisses) / SUM(gets) * 100, 2) || '%' AS value
FROM v$rowcache
UNION ALL

-- Redo Log Buffer Hit Ratio
SELECT 'Redo Log Buffer Hit Ratio' AS metric,
       ROUND((1 - (redo_entries / redo_buffer_alloc_retries)) * 100, 2) || '%' AS value
FROM
  (SELECT VALUE redo_entries FROM v$sysstat WHERE name = 'redo entries'),
  (SELECT VALUE redo_buffer_alloc_retries FROM v$sysstat WHERE name = 'redo buffer allocation retries');

6. 注意事项

• 命中率不是唯一标准​：即使命中率很高，仍可能因等待事件（如 log file sync​、buffer busy waits）而性能不佳。
• 阈值参考：上述阈值仅为经验值，具体环境需结合 AWR 报告和系统负载综合判断。
• 动态调整​：SGA 组件大小可在 11g 中使用 ASMM（Automatic Shared Memory Management） 自动管理，或手动调整 db_cache_size​、shared_pool_size 等参数。
• 统计准确性：这些查询基于实例启动以来的累计值，如需观察短期趋势，建议结合 AWR 快照。

7. 扩展：查看 SGA 各组件当前大小

SHOW SGA;
-- 或
SELECT * FROM v$sgainfo;

如果需要进一步优化建议（如如何调整内存参数、定位低效 SQL），请提供具体命中率数值及系统负载情况。