Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Buffer Hit Ratio % -- Whats the right query ?

user4874781Jan 4 2009 — edited Jan 6 2009
Whats the right query to track Buffer Hit % ;

Using this :

prompt BUFFER HIT RATIO %
prompt ===============
select 100 * ((a.value+b.value)-c.value) / (a.value+b.value) "Buffer Hit Ratio"
from v$sysstat a, v$sysstat b, v$sysstat c
where
a.statistic# = 38
and
b.statistic# = 39
and
c.statistic# = 40;

Buffer Hit Ratio
----------------
99.9678438


However, using this :
Select
Round((Sum(Decode(name, 'consistent gets',value,0)) +
Sum(Decode(name, 'db block gets',value,0)) -
Sum(Decode(name, 'physical reads',value,0))) /
(Sum(Decode(name, 'consistent gets',value,0)) +
Sum(Decode(name, 'db block gets',value,0)) ) * 100, 4)
from V$sysstat;

Comes up as : 67.7069 %

So which is the right one ?

Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 3 2009
Added on Jan 4 2009
23 comments
1,860 views