column physicalReads format 999,999,999 heading 'Physical Reads'
column gets format 999,999,999 heading 'DB Block Gets'
column consistentGets format 999,999,999 heading 'Consistent Gets'
column hitRatio format 999.99 heading 'Hit Ratio'
select sum(decode(name, 'physical reads', value, 0)) physicalReads,
sum(decode(name, 'db block gets', value, 0)) gets,
sum(decode(name, 'consistent gets', value, 0)) consistentGets,
(1 -
(sum(decode(name, 'physical reads', value, 0)) /
(sum(decode(name, 'db block gets', value, 0)) +
sum(decode(name, 'consistent gets', value, 0))))) *
100 hitRatio
from v$sysstat;
Example:
Physical Reads DB Block Gets Consistent Gets Hit Ratio
-------------- ------------- --------------- ---------
27,056 156,566 1,844,389 98.65
Another method that gives the Hit Ratio for the last couple of minutes (10g+)
column beginTime heading 'From'
column endTime heading 'To'
column hitRatio format 999.999 heading 'Hit Ratio'
alter session set nls_date_format = 'MM/DD/YYYY HH24:MI:SS';
select value hitRatio,
begin_time beginTime,
end_time endTime
from v$sysmetric
where metric_name = 'Buffer Cache Hit Ratio';
Example:
Hit Ratio From To
--------- ------------------- -------------------
96.389 09/27/2009 13:35:54 09/27/2009 13:36:54
99.421 09/27/2009 13:36:54 09/27/2009 13:37:09
