如何编写SQL按实例分组计算Buffer Cache Hit Ratio？

我来帮你搞定这个问题～你之前用子查询没成功，大概率是因为gv$sysstat里的指标是按实例+指标名称分行存储的，得先把同一实例的三个指标值转成同一行的列，才能代入公式计算。下面给你两种靠谱的写法：

方法一：条件聚合（兼容性好，适合所有Oracle版本） #

这种方式用CASE语句把每个指标的值单独汇总，再代入命中率公式：

SELECT
    inst_id,
    ROUND(1 - (SUM(CASE WHEN name = 'physical reads cache' THEN value ELSE 0 END) / 
        (SUM(CASE WHEN name = 'consistent gets from cache' THEN value ELSE 0 END) + 
         SUM(CASE WHEN name = 'db block gets from cache' THEN value ELSE 0 END))), 2) AS VALUE
FROM
    gv$sysstat
WHERE
    name IN ('consistent gets from cache', 'db block gets from cache', 'physical reads cache')
GROUP BY
    inst_id
ORDER BY
    inst_id;

逻辑说明： #

• SUM(CASE ...)会按inst_id分组，把每个指标的数值单独加总
• 代入你给出的公式1 - (physical reads cache / (consistent gets from cache + db block gets from cache))
• ROUND(...,2)把结果保留两位小数，和你期望的输出格式一致

方法二：PIVOT行转列（Oracle 11g+可用，写法更简洁） #

如果你的Oracle版本是11g及以上，可以用PIVOT直接把行数据转成列，计算起来更直观：

SELECT
    inst_id,
    ROUND(1 - (physical_reads_cache / (consistent_gets_from_cache + db_block_gets_from_cache)), 2) AS VALUE
FROM
    gv$sysstat
PIVOT (
    SUM(value)
    FOR name IN (
        'consistent gets from cache' AS consistent_gets_from_cache,
        'db block gets from cache' AS db_block_gets_from_cache,
        'physical reads cache' AS physical_reads_cache
    )
)
ORDER BY
    inst_id;

逻辑说明： #

• PIVOT把原来按行存储的三个指标，转成同一行的三个列（consistent_gets_from_cache、db_block_gets_from_cache、physical_reads_cache）
• 直接用转好的列代入公式计算，代码更清晰

这两种方法都能输出你想要的格式：

INST_ID VALUE
1       0.92
2       0.93

内容的提问来源于stack exchange，提问作者john true