使用评级查询命中率
select (1 - (sum(decode(name, 'physical reads',value,0)) /
(sum(decode(name, 'db block gets',value,0)) +
sum(decode(name, 'consistent gets',value,0))))) * 100 "Hit Ratio",
decode(sign((1-(sum(decode(name, 'physical reads',value,0)) /
(sum(decode(name, 'db block gets',value,0)) +
sum(decode(name, 'consistent gets',value,0))))) * 100 - 98),1,30,
decode(sign((1-(sum(decode(name, 'physical reads',value,0)) /
(sum(decode(name, 'db block gets',value,0)) +
sum(decode(name, 'consistent gets',value,0))))) * 100 - 95),1,20,
decode(sign((1-(sum(decode(name, 'physical reads',value,0)) /
(sum(decode(name, 'db block gets',value,0)) +
sum(decode(name, 'consistent gets',value,0))))) * 100 - 90),1,10,0)))
"Score"
from v$sysstat
/
示例输出
Hit Ratio Score
----------- ----------
99.8805856 30
V$SYSSTAT 中的数据反映所有缓冲池的逻辑和物理读操作的统计数据。要单独获得缓冲池的命中率，可以查询动态性能视图V$BUFFER_POOL_STATISTICS。
(sum(decode(name, 'db block gets',value,0)) +
sum(decode(name, 'consistent gets',value,0))))) * 100 "Hit Ratio",
decode(sign((1-(sum(decode(name, 'physical reads',value,0)) /
(sum(decode(name, 'db block gets',value,0)) +
sum(decode(name, 'consistent gets',value,0))))) * 100 - 98),1,30,
decode(sign((1-(sum(decode(name, 'physical reads',value,0)) /
(sum(decode(name, 'db block gets',value,0)) +
sum(decode(name, 'consistent gets',value,0))))) * 100 - 95),1,20,
decode(sign((1-(sum(decode(name, 'physical reads',value,0)) /
(sum(decode(name, 'db block gets',value,0)) +
sum(decode(name, 'consistent gets',value,0))))) * 100 - 90),1,10,0)))
"Score"
from v$sysstat
/
示例输出
Hit Ratio Score
----------- ----------
99.8805856 30
V$SYSSTAT 中的数据反映所有缓冲池的逻辑和物理读操作的统计数据。要单独获得缓冲池的命中率，可以查询动态性能视图V$BUFFER_POOL_STATISTICS。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25198367/viewspace-707717/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
等待事件V$视图
请登录后发表评论
登录
全部评论
