buffer cache hit ratio. LIO vs PIO - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 40

Thread: buffer cache hit ratio. LIO vs PIO

  1. #21
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    You see? Better SQL = lower cache hit ratio. High cache hit ratio = potentially bad SQL.
    Dont generalise, we (david and me) have had discussed both verses..

    It could be either way as well i.e

    Better SQL = High cache hit ratio, lower cache hit ratio = potentially bad SQL.
    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"

  2. #22
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Great example, Dave!

    I hardly ever look at ratio's, but noticed that one of my systems had a HR of 42.8%. I didn't really need to add more memory to 'fix' this. You see, the system has 4 BIG tables and 58 REALLY little look-up tables. It's not a very active system, so the look up tables weren't staying in the cache as lots of data was being read from the Big tables. So I created a Keep pool, and put all my little stuff there. Now my hit ratio is 88% (Keep Pool is 99.5). I've reduced IO on my system a bit now, and reduced my buffer busy waits a bit. 88% is not "ideal" according to some, but I'm happy with it. Nobody is complaining and it makes sense with the data in my db.

    I guess my point is: If your ratio is low - consider other things than just adding memory. (Remember, adding more memory can also increase your latch waits!!)

    Jodie

  3. #23
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Jodie, that's a nice little case study.

    - The ratio did the job of signalling a potential problem.
    - You looked at the real cause of the low ratio and fixed the problem (not the symptom). You might have postponed an upgrade by doing this.
    - You understand that 88% is "healthy" for that db - so if you see it at 50% later you know it's gone "sick" (perhaps).

  4. #24
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by abhaysk
    Dont generalise, we (david and me) have had discussed both verses..

    It could be either way as well i.e

    Better SQL = High cache hit ratio, lower cache hit ratio = potentially bad SQL.
    Yeah, but i got bored reading it.

    Did you run any examples where better SQL produce a high cache hit ratio?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #25
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by DaPi
    Jodie, that's a nice little case study.

    - The ratio did the job of signalling a potential problem.
    - You looked at the real cause of the low ratio and fixed the problem (not the symptom). You might have postponed an upgrade by doing this.
    - You understand that 88% is "healthy" for that db - so if you see it at 50% later you know it's gone "sick" (perhaps).
    Also, you could look at cache advice to tell you whether you would benefit from

    i) a little more RAM all round
    ii) reallocating somememory from KEEP to RECYCLE, or vice versa.

    Don't forget that cacheadice works at the KEEP/RECYCLE etc. level, as well as for the whole buffer cache.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #26
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    Did you run any examples where better SQL produce a high cache hit ratio?
    Small OLTP dbs and very well tuned application as a whole do produce high cahce hit ratios (above 95 also)..where in you have N good sqls being executed very frequently and having a fairly good sized DB Cache !
    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"

  7. #27
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by abhaysk
    Small OLTP dbs and very well tuned application as a whole do produce high cahce hit ratios (above 95 also)..where in you have N good sqls being executed very frequently and having a fairly good sized DB Cache !
    I think that people are probably agreeing here. High cache hit ratio could be good or bad, low cache hit ratio could be good or bad.

    Since the belief that "high cache hit ratio is always good" is still being taught, documented, believed, and worked on however it seems worthwhile to point out that given a cache hit ratio in isolation you can tell absolutely nothing about quality of SQL or sizing of buffer cache.

    Changes in ratio are interesting, but an increase or decrease just tells you "something changed about the application".

    Cache hit ratio is meaningless on it's own, right?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  8. #28
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    Cache hit ratio is meaningless on it's own, right?
    That's probably true about any single statistic you drag out of the db.

  9. #29
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Also, you could look at cache advice ...
    I think it's only available in 9i. And the DB is stuck on 8i for a few more months.

    Interestingly, v$db_cache_advice is available in 8i, but you can't turn cache advice on, so the important columns are empty!

    Jodie

  10. #30
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by DaPi
    That's probably true about any single statistic you drag out of the db.
    Ah that's true, but of all the statistics available I think that tuning the buffer cache hit ratio is the issue that has been unjustifiably force-fed down the throats of so many unwitting DBA's.
    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