Oracle Scratchpad

September 2, 2007

Hit ratios

Filed under: Performance,Ratios,Troubleshooting — Jonathan Lewis @ 12:41 pm BST Sep 2,2007

A recent post on 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 system with just a default cache:

	name, value
where	name in (
		'physical reads',
		'consistent gets',
		'db block gets'

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


------------- --------------- --------------
      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:

	name, value
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]



  1. will you please post some reasons about why buffer cache hit ratio is pointless? I know it is and you know it is but I have some coworkers that STILL blindly swear by it. They wont listen to me or read much in the way of documentation – maybe if it comes from an official source :D

    Comment by neil — September 2, 2007 @ 3:43 pm BST Sep 2,2007 | Reply

  2. Neil, they should have read a paper by Cary Millsap:

    Comment by Alex Fatkulin — September 2, 2007 @ 7:25 pm BST Sep 2,2007 | Reply

  3. Neil, I’m planning to write a short note in the next two or three days.

    Comment by Jonathan Lewis — September 2, 2007 @ 8:25 pm BST Sep 2,2007 | Reply

  4. I think it’s worth to add that in 10g there is a view v$sysmetric from which you could obtain several ‘hit ratios’ and other performance parameters.

    Comment by Szymon Skorupinski — September 5, 2007 @ 1:16 pm BST Sep 5,2007 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by