Hit ratios

A recent post on comp.databases.oracle.server asked the question “why does v$sysstat give you a different hit ratio from v$buffer_pool_statistics even when you have just one buffer pool for the instance”.

The largest part of the answer is because very few people have bothered to think carefully about what the “buffer cache hit ratio” means, and which statistics are relevant. If you are running 10g, though, you get a few clues about some of the issues involved.

Here are a couple of queries, with slightly cleaned results, that I recently ran on a small 10.2.0.3 system with just a default cache:

select
	name, value
from
	v$sysstat
where	name in (
		'physical reads',
		'consistent gets',
		'db block gets'
	)
;           

NAME                                          VALUE
-------------------------                ----------
db block gets                               3104855
consistent gets                            15555613
physical reads                               251889      

select
	db_block_gets,
	consistent_gets,
	physical_reads
from
	v$buffer_pool_statistics 
;    

DB_BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS
------------- --------------- --------------
      3102999        15552012         246481

With these statistics (which are the ones typically used in the formula for the buffer cache hit ratio) you’re going to get slightly different results from the two views. 

But take a closer look at the full range of statistics in v$systat, and change your query to this:

select
	name, value
from
	v$sysstat
where	name in (
		'consistent gets from cache',
		'db block gets from cache',
		'physical reads cache'
	)
;          

NAME                                          VALUE
---------------------------------------- ----------
db block gets from cache                    3102999
consistent gets from cache                 15552012
physical reads cache                         246480

Suddenly we have a good match (with a tiny error in this case on physical reads). It always helps to be looking at the right figures when making comparisons.

I think there are still likely to be odd cases where you find relatively small differences between the figures you get from v$sysstat and v$buffer_pool_statistics. Amongst other things, it’s likely that the system statistics are subject to latch-free updates – which means lost updates are inevitable.

By the way – just because I’ve explained an oddity with calculating the buffer cache hit ratio that shouldn’t be taken as an indication of approval for the metric – it’s a fairly pointless indicator for monitoring purposes and I don’t think I’ve ever seen a single formula that gets it right anyway.  But that’s a topic for another day.

[Further reading on ratios]