buffer cache hit ratio. LIO vs PIO
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 40

Thread: buffer cache hit ratio. LIO vs PIO

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    buffer cache hit ratio. LIO vs PIO

    I came across a post on AskTom that seemed to contradict everything I've been told or read about tuning the buffer cache hit ratio and reducing Physical IO by aiming to get all queries satisfied by reading the blocks in the buffers.

    The article quotes a paper by Cary Millsap of Hotsos, and is titled "Why a 99%+ Database Buffer Cache Hit Ratio is NOT OK".

    Whats your view on LIP vs PIO and exactly what should we look for when tuning memory?

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    They are correct. You can look at buffer cache advice though, to see the affect of modifying cache size on number of physical i/o's
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: buffer cache hit ratio. LIO vs PIO

    Originally posted by JMac
    I came across a post on AskTom that seemed to contradict everything I've been told or read about tuning the buffer cache hit ratio and reducing Physical IO by aiming to get all queries satisfied by reading the blocks in the buffers.

    Whats your view on LIP vs PIO and exactly what should we look for when tuning memory?
    Wass wrong in that link???????

    wont reducing ur LIO reduce ur PIO (most often than not it will)..

    U shud look for ur CONSISTENT/DB BLOCK gets and reduce as much as possible..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Okay, lets take a step back here ...

    It is - I believe - much faster to satisfy a query from blocks in memory (LIO) than to retrive blocks from disk (PIO).

    Oracle recommend a Buffer cache Hit Ratio of 90%+. But the Hotsos article states that 99%+ indicates a poorly performing system. If the aim is to reduce PIO as much as possible, would not a Buffer Hit Ratio of 100% be ideal? Is there something basic here I'm missing?

    Also 'Consistent gets' in a traced query. This indicates blocks retrieved from memory, and 'db block gets'indicates when IO is performed to disk. Thats right isn't it?

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Well Jmac,

    tell me can u avoid PIO???? -- No will be ur answer..

    cache hit 99+ would mean that u have all bad sqls running that are eating up ur CPUs...and are subject to serialization
    Ur seeing this high cache hit 99+ due ur Buffer gets (LIOs) are so high that PIO is not seen as significant number...

    So reduce ur LIOs if u can and never look HIT ratio, its just a crap.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by JMac
    If the aim is to reduce PIO as much as possible, would not a Buffer Hit Ratio of 100% be ideal? Is there something basic here I'm missing?
    What you are missing is that you can achieve a ratio of 99.999% just by writing bad SQL. Bad SQL pushes up LIO's, while potentially leaving PIO's constant.

    Hash joins also push up LIO's -- a datawarehouse with lots of star transformations runs a very high cache hit ratio.

    So ...

    i) reduce LIO's by tuning SQL.
    ii) monitor sensitivity of PIO's to cache size, and adjust cache size if beneficial.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    "all generalisations are false"

    "there are lies, damn lies and statistics" - a percentage is a statistic, and therefore to be treated with care.

    If we just condsider the "work" to do I/O (which is not all the story) then there is a measure of work that will be something like:
    Work = A*LIO + B*PIO
    where B > A (or even >> A).

    If the missRatio is PIO/LIO then:
    Work = A*LIO*(1 + C*missRatio)
    where C > 1 (or even >>1).

    For a fixed amount of LIO, yes it is good to reduce the missRatio (perhaps by increasing the cache size).

    The missRatio is PIO/LIO - it is usually NOT a good idea to reduce this by increasing LIO (though in rare cases it can reduce PIO). Millsap's point is that small missRatios can be the symptom of large LIO values.

    I seem to remember Tom Kyte saying "look after the LIO and the PIO will look after itself" - another generalisation, but probably a good starting point.

    My experience is that the Hit Ratio is a way to quickly check that all is well in your db once you have understood what is going on in your db.
    Last edited by DaPi; 01-28-2004 at 12:34 PM.

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: buffer cache hit ratio. LIO vs PIO

    Further thoughts - I don't want to pick on you particularly JMac, but your post is revealing:
    Originally posted by JMac
    . . . tuning the buffer cache hit ratio . . . .
    . . . . what should we look for when tuning memory?
    I think this emphasises how easy it is to get away from the real objective. I don't want to do either of the above, I want to "maximise throughput" or "minimise response time" or something similar which relates to business efficiency. No user will congratulate you on keeping the hit ratio above 95%!

    Frankly, I didn't realise 100% what I was doing when I looked at "I/O Work" in my previous. Almost by accident I stumbled upon a rule which says: if you are going to look at hit ratios or parse/execute ratios etc, you'd be advised to write down some kind of short-hand which relates them to your real objective.
    (Climbs down off soap-box.)

  9. #9
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    392

    Buffer cache hit ratio.....

    When I was being taught Oracle 9i(OCP) our Instructor made the point that if buffer cache hit ratio should be at the acceptable level of 95%, and if it fell below 95% to add more ram(tuning). All but 1 article that I have read on the subject, and outside books(2) that I have said the same thing, "when buffer cache hit ratio falls below 95% throw more ram(tuning) at it until it rises back up to 95%.

    I truely understand that some of you strongly suggest that we(newbies) RTM, but when we read the manual, and it says the same thing, I feel that I have been cornered into asking you folks for assistance.

    As mentioned earlier in this post (All but 1 article that I have read on the subject) it only touched on design, it did not get real in depth about it.

    I throw in with JMac on this one, what should a DBA tasked with buffer cache hit ratio look for in this matter?

    Rick
    Rick

    Sigh.....those were the days my friend, I thought they'd never end.
    I too remember when this place was coo.


  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Buffer cache hit ratio.....

    Originally posted by Alchemy
    When I was being taught Oracle 9i(OCP) our Instructor made the point that if buffer cache hit ratio should be at the acceptable level of 95%, and if it fell below 95% to add more ram(tuning). All but 1 article that I have read on the subject, and outside books(2) that I have said the same thing, "when buffer cache hit ratio falls below 95% throw more ram(tuning) at it until it rises back up to 95%.

    I truely understand that some of you strongly suggest that we(newbies) RTM, but when we read the manual, and it says the same thing, I feel that I have been cornered into asking you folks for assistance.

    As mentioned earlier in this post (All but 1 article that I have read on the subject) it only touched on design, it did not get real in depth about it.

    I throw in with JMac on this one, what should a DBA tasked with buffer cache hit ratio look for in this matter?

    Rick
    You can use the cache advisor to indicate the effectiveness of allocating or deallocating RAM to buffer cache.

    Long story short, the sources you quote were wrong. If you are tasked with improving buffer cache hit ratio, just run some badly designed sql over and over again, thus increasing LIO's and herdly touching PIO's. That tells you all you need to know about how meaningful the ratio is.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width