-
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"
-
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
-
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).
-
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?
-
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.
-
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"
-
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?
-
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.
-
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|