Cache Hit Ratio. Who Needs It?

Some things can never be said often enough. Buffer cache hit ratio is a worthless indicator of performance when Oracle is pounding the daylights out of a few cache buffer chains. I see Hemant Chitale has just blogged on this topic:

Buffer Cache Hit Ratio GOOD or BAD ?

About Logical Reads
All logical reads in Oracle start with a hash algorithm on the database block address. Since there is an unknown number of blocks in the database (dba), this cannot be a “perfect hash” so there are hash collisions. Oracle resolves this by “chaining” dbas with equal hash values. Chains hang off of “buckets” and each bucket has a latch. To walk a chain (looking for the exact dba your session needs), the latch is first aquired. These are db block gets and db block consistent gets depending on the type of block you are looking for (versioning). Applications that clone a lot of blocks can have a “piling up” affect on the buckets that govern these hot chains. Fix that problem at the application level before worrying about hit ratio and long before trying to deal with latch dynamics (e.g., spin count, increasing buckets, etc).

3 Responses to “Cache Hit Ratio. Who Needs It?”


  1. 1 Tim Hall February 26, 2007 at 8:35 am

    Hi.

    In his OpenWorld presentation, Richard Niemiec actually commented on still using Hit Ratios, but only as one of several top level indicators. I almost gasped when he said it, but his approach kinda made sense.

    Rather than saying a CHR of X is good and Y is bad, he argued that if the database normally runs with a CHR of X and now it is running at Y, something must have changed and that warrants some further investigation. So he uses them like an indicator of change, not as a performance measurement.

    Of course, there are many other metrics one can use like this…

    Cheers

    Tim…

  2. 2 Jared Still March 2, 2007 at 11:48 pm

    Re Tim Hall’s comment that many other metrics can be used to determine if performance is going through the floor:

    I rely on users. If the users are unhappy (they do let us know) then something needs to be investigated.

    Sure, there are many things you can do to measure performance. But if the users are happy, and everything is working, do you really want to chase down the ‘performance’ problem?

    Most of us have far too much to do as it is without investigating a blip on the BCHR.

    That’s my $0.02 on it anyway.

    Jared

  3. 3 Yasir December 5, 2012 at 9:49 am

    A change in BCHR is an indicator of a positive change or negative change?
    If BCHR was 95 and went to 92 and the database performance is same, why to waste effort in investigating the change in BCHR?
    Why not simply focus on wait event analysis?
    Richard Neimic approch is a result of Complusive tuning disorder.


Leave a Reply

Please log in using one of these methods to post your comment:

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 )

Connecting to %s

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




DISCLAIMER

I work for Amazon Web Services. The opinions I share in this blog are my own. I'm *not* communicating as a spokesperson for Amazon. In other words, I work at Amazon, but this is my own opinion.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 747 other subscribers
Oracle ACE Program Status

Click It

website metrics

Fond Memories

Copyright

All content is © Kevin Closson and "Kevin Closson's Blog: Platforms, Databases, and Storage", 2006-2015. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Kevin Closson and Kevin Closson's Blog: Platforms, Databases, and Storage with appropriate and specific direction to the original content.

%d bloggers like this: