1 2 Previous Next 29 Replies Latest reply: Dec 7, 2010 7:47 AM by 666127 RSS

    Buffer Hit % discussion

    user88831
      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
          Kindly share ur thoughts.
          If high is NOT good
          then low is NOT bad.
          • 2. Re: Buffer Hit % discussion
            user88831
            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....
              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
                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
                  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
                    Billy~Verreynne
                    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....
                      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
                        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
                          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
                            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
                              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
                                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....
                                  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
                                    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