Oracle Scratchpad

September 2, 2007

Hit ratios

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

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]

 

4 Comments »

  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 GMT Sep 2,2007 | Reply

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

    http://www.hotsos.com/e-library/abstract.php?id=6

    Comment by Alex Fatkulin — September 2, 2007 @ 7:25 pm GMT 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 GMT 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 GMT Sep 5,2007 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,305 other followers