Oracle documentation says in the “Performance Tuning Guide 11g Release 2” that the “Buffer Cache Hit Ratio” has to be calculated as:
1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')
using the results from the query
SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('db block gets from cache', 'consistent gets from cache', 'physical reads cache');
but in v$sysstat there are also those values without the word “cache” at the end and it seems to me that they are always a little bit higher than the values with “cache”. But what exactly is the difference between a “db block get” and a “db block get from cache”. In a simple understanding I would say, if the database doesn’t get a block from the cache than it must be a physical read but this idea is not proved by the values.
And then there is another view with the name v$buffer_pool_statistics and it has columns with names like “physical reads“,”consistent gets” and “db block gets”. When I look here, I find different values once more.
With the following query I get a “Hit Rate” on three different ways in one SQL statement:
with v as (
select
'sysstat' origin, name, value v1, null v2
from v$sysstat
where name in ('physical reads', 'db block gets', 'consistent gets')
union
select 'sysstat from cache', name, value, null
from v$sysstat
where name in ('consistent gets from cache','db block gets from cache','physical reads cache')
union
select 'buffer pool stat', 'physical reads', physical_reads , name
from v$buffer_pool_statistics
union
select 'buffer pool stat', 'consistent gets', consistent_gets , name
from v$buffer_pool_statistics
union
select 'buffer pool stat', 'db block gets', db_block_gets , name
from v$buffer_pool_statistics)
select origin, name, v1, v2
from v
union
select
origin,
' hit rate',
round((1 - sum(case when name like 'physical%' then v1 else 0 end)/
(sum(case when name like 'db_blocks%' then v1 else 0 end) +
sum(case when name like 'consistent%' then v1 else 0 end)))*100,2),
v2
from v
group by origin, v2
order by 1,2;
On one 11.2.0.3 instance on a Windows machine with an uptime of 34 days I get this result:
ORIGIN NAME V1 V2
------------------ ------------------------------ ------------------- -------
buffer pool stat hit rate 99.27 DEFAULT
buffer pool stat consistent gets 14,344,376,641.00 DEFAULT
buffer pool stat db block gets 742,028,560.00 DEFAULT
buffer pool stat physical reads 104,274,354.00 DEFAULT
sysstat hit rate 88.90
sysstat consistent gets 35,308,048,765.00
sysstat db block gets 770,174,618.00
sysstat physical reads 3,917,536,134.00
sysstat from cache hit rate 99.67
sysstat from cache consistent gets from cache 31,525,679,416.00
sysstat from cache db block gets from cache 742,034,505.00
sysstat from cache physical reads cache 104,274,533.00
On another 11.2.0.3 instance (AIX, uptime 98 days) I see this:
ORIGIN NAME V1 V2
------------------ ------------------------------ ------------------- -------
buffer pool stat hit rate 89.38 DEFAULT
buffer pool stat consistent gets 16,564,614,315.00 DEFAULT
buffer pool stat db block gets 4,150,966,901.00 DEFAULT
buffer pool stat physical reads 1,759,680,692.00 DEFAULT
sysstat hit rate 95.20
sysstat consistent gets 105,628,149,196.00
sysstat db block gets 4,152,392,538.00
sysstat physical reads 5,073,212,722.00
sysstat from cache hit rate 98.28
sysstat from cache consistent gets from cache 102,510,360,491.00
sysstat from cache db block gets from cache 4,151,368,009.00
sysstat from cache physical reads cache 1,759,683,309.00
On a third 11.2.0.3 instance (one more Windows server, uptime 115 days) the values from the three different sources are more alike but still not identical:
ORIGIN NAME V1 V2
------------------ ------------------------------ ------------------- -------
buffer pool stat hit rate 98.22 DEFAULT
buffer pool stat consistent gets 917,238,244.00 DEFAULT
buffer pool stat db block gets 62,374,505.00 DEFAULT
buffer pool stat physical reads 16,361,311.00 DEFAULT
sysstat hit rate 98.16
sysstat consistent gets 917,362,983.00
sysstat db block gets 62,399,325.00
sysstat physical reads 16,842,858.00
sysstat from cache hit rate 98.22
sysstat from cache consistent gets from cache 917,285,925.00
sysstat from cache db block gets from cache 62,374,634.00
sysstat from cache physical reads cache 16,361,775.00
Especially in the second example the difference between v$sysstat and v$buffer_pool_statistics is notable. While “db block gets” and “physical reads“ are nearly the same as “db block gets from cache” and “physical reads cache” the value for ”consistent gets” is only 16% of “consistent gets from cache”.
What is the explanation for the different results, that I get with this three different ways?