This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Dec 7, 2010 5:47 AM by 666127 RSS

Buffer Hit % discussion

user88831 Newbie
Currently Being Moderated
Hi Guys,

I read few sites about buffer hit % vs performance of the database.

I understand that a high % of hit ratio doesn't mean the performance is good. It's might means that the queries running in the database is doing alot of unwanted huge I/O by the user of unselective indexes.

However can i Conclude that a low % of hit ratio = bad performance where we have to either look into the sql <reduce logical i/o which in term reduce physical i/o> and adding memory to the buffer cahe if we confirm all the sqls are good but still low hit %.

Kindly share ur thoughts.

thanks!
  • 1. Re: Buffer Hit % discussion
    sb92075 Guru
    Currently Being Moderated
    Kindly share ur thoughts.
    If high is NOT good
    then low is NOT bad.
  • 2. Re: Buffer Hit % discussion
    user88831 Newbie
    Currently Being Moderated
    Hi,

    I understand that high hit ratio "might" not be good.

    But any scenario that explains why low hit ratio is "not bad"?

    btw my DB is 10g and the i have gotten the hit ratio from awr.

    thanks
  • 3. Re: Buffer Hit % discussion
    Aman.... Oracle ACE
    Currently Being Moderated
    dbaing wrote:
    Hi Guys,

    I read few sites about buffer hit % vs performance of the database.

    I understand that a high % of hit ratio doesn't mean the performance is good. It's might means that the queries running in the database is doing alot of unwanted huge I/O by the user of unselective indexes.

    However can i Conclude that a low % of hit ratio = bad performance where we have to either look into the sql <reduce logical i/o which in term reduce physical i/o> and adding memory to the buffer cahe if we confirm all the sqls are good but still low hit %.
    Hit ratio is the metric of the IO done from the memory. So if its low that means , you are doing more of the physical IO and not accessing data from cache. Now adding memory is just one of the many reasons that it may happen. This would only work if you would have the buffer cache undersized but if you have the queries written wrong , for example, doing lots of full table scan or accessing unnecessary data, adding memory wont help at all. So in conclusion, you need to actually check that what's really going on. Hit ratio is just a metric and its not (should not) something to be treated as like a final conclusion to be achieved.

    HTH
    Aman....
  • 4. Re: Buffer Hit % discussion
    user88831 Newbie
    Currently Being Moderated
    hi Aman,

    does it means to say a low hit ratio of about 60% indicate some problem with the db? Either a undersize cache or poorly tuned sql?

    thanks
  • 5. Re: Buffer Hit % discussion
    sb92075 Guru
    Currently Being Moderated
    But any scenario that explains why low hit ratio is "not bad"?
    Please quantify.
    At what value is it named "high" or "low"?
    Provide PROOF it is correctly classified.
  • 6. Re: Buffer Hit % discussion
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    dbaing wrote:

    I understand that high hit ratio "might" not be good.
    But any scenario that explains why low hit ratio is "not bad"?
    btw my DB is 10g and the i have gotten the hit ratio from awr.
    What does the hit ratio show - what metrics are used to create the ratio?

    Physical I/O (PIO). Logical I/O (LIO).

    Okay, so what is I/O then?

    I/O is work that needs to be done in order to satisfy a need for data (rows). There is no difference between PIO and LIO in this regard. It is work that needs to be done - and whether this I/O is from disk or is from memory, does not change the demand for data and the work that needs to be done. Think of PIO and LIO as simply different coloured I/O - the colour does not change the fact that I/O is being done and that I/O was requested to be done.

    So if both PIO and LIO are work that needs to be done, how can one be bad and one be good?

    The actual thing that determines the "+Good/Bad+" factor is that request for that work to be done. Is that request reasonable? Is it correct? Can that request be effectively satisfied given the logical data model and physical implementation of it?

    So it is about the work itself - that determines whether the PIO or LIO is done effectively. As a simple metric on its own, PIO and and LIO are meaningless. As a percentage ratio, it is equally, meaningless. It is worth nothing. It means nothing.
  • 7. Re: Buffer Hit % discussion
    Aman.... Oracle ACE
    Currently Being Moderated
    You drove yesterday at the speed of 100Kms/hour and today you drove at the speed of 30kms/hour. So now, what do you interpret out of it, your car is working/behaving bad today? (Replace hit ratio with speed here) . As Billy explained, the IO is the thing which is governing it. So if you just look at a metric, you are going in a complete wrong direction. Now, if you would bother to look , yesterday when you drove, it was weekend, no office, no traffic, you could drive very fast with the same car which didn't work very well today when its a weekday, you are talking some sensible thing. So what you should do, not change the car but the way or timing to start from home.

    Hope it did come out as a meaningful example.

    Aman....
  • 8. Re: Buffer Hit % discussion
    635471 Expert
    Currently Being Moderated
    You judge whether changing the size of your buffer cache would potentially improve or degrade performance by looking at the buffer cache advice, not the hit ratio.
  • 9. Re: Buffer Hit % discussion
    311441 Employee ACE
    Currently Being Moderated
    My thoughts:

    http://richardfoote.wordpress.com/2007/12/16/buffer-cache-hit-ratios-useful-or-not/

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 10. Re: Buffer Hit % discussion
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    dbaing wrote:

    However can i Conclude that a low % of hit ratio = bad performance where we have to either look into the sql <reduce logical i/o which in term reduce physical i/o> and adding memory to the buffer cahe if we confirm all the sqls are good but still low hit %.

    Kindly share ur thoughts.
    If you have a model of how you expect your system to behave then there may be cases, or times, when you can decide that some ratio is signficantly out of the range you expect.

    As far as the BCHR is concerned, you could imagine approximating an OLTP system with the (pessimistic) assumption that a single row access requires thee index block visits (root, branch, leaf) and one table block visit to pick up the row. If that's really the case you could also decide that for your model you have enough memory to buffer the indexes, but no memory for keep the tables buffered because of the extreme randomness of the table visits. If that model makes sense for you then you might expect a hit ratio of around 75% - and therefore start to worry if the ratio is significantly lower. Of course the OLTP system might run regular reports - which do large tablescans and hash joins and distort the figures; it might accumulate a large number of indexes over time which could invalidate your "all indexes buffered" model; there are probably a number of small tables which are constantly buffered that (ought to) push the hit ratio up.

    Without a reasonable model of what your system is supposed to do at what times of day, and what variation to expect over the week it's quite hard to make any comment about what constitutes a low or high BCHR - and it's hard to say how long and how far the figure should deviate from your expectation before you consider it to be showing threatening behaviour.


    Regards
    Jonathan Lewis
  • 11. Re: Buffer Hit % discussion
    user88831 Newbie
    Currently Being Moderated
    thanks all for the advise provided.

    can anyone provide a example when low hit ratio (over a hour or 2 from awr) is good?
  • 12. Re: Buffer Hit % discussion
    user88831 Newbie
    Currently Being Moderated
    as i wish to know if a low hit ratio is definately bad? something to look at it whether to add more memory or tune sql with full scan or unnessary logial read.

    thanks
  • 13. Re: Buffer Hit % discussion
    Aman.... Oracle ACE
    Currently Being Moderated
    723485 wrote:
    as i wish to know if a low hit ratio is definately bad? something to look at it whether to add more memory or tune sql with full scan or unnessary logial read.

    thanks
    What you have concluded from all the discussion that has happened above?

    Aman....
  • 14. Re: Buffer Hit % discussion
    Dom Brooks Guru
    Currently Being Moderated
    as i wish to know if a low hit ratio is definately bad
    No. Just as a high ratio is not necessarily good.
    Tuning by buffer cache hit ratio is a myth debunked.
    If after all the advice above, you're still wondering about it, then you should ignore it.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points