Printable View
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?
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
Wass wrong in that link???????Quote:
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?
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..
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?
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.
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.Quote:
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?
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.
"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.
Further thoughts - I don't want to pick on you particularly JMac, but your post is revealing: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%!Quote:
Originally posted by JMac
. . . tuning the buffer cache hit ratio . . . .
. . . . what should we look for when tuning memory?
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.)
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.Quote:
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
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.
Why not tell Hit Ratio as CRAP :rolleyes: :rolleyes:Quote:
Originally posted by slimdave
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.
Abhay.
But it's not! It's just one of many "tools" that you can use to judge if your db is healthy - you need to understand its usefulness and its limitations in the context of your db.Quote:
Originally posted by abhaysk
Why not tell Hit Ratio as CRAP :rolleyes: :rolleyes:
A generalisation and therefore false! HOWEVER in some proportion of cases, it might be a cost-effective solution: memory is cheap and consultants are expensive.Quote:
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%.
The proper solution is expensive because it requires someone to think about it:
a) Is there a problem?
A low hit ratio in itself is NOT a problem. (A few times per day, I have hit ratios around 20% for a few minutes when a user selects customers according to some ad-hoc profile. We made a conscious decision NOT to add a vast numbers of indexes to cover all eventualities, so most of these selections are FTS. Getting a customised client list in two minutes is very acceptable to the end-user - it would take 24hrs if he asked me to do it).
b) Why has the ratio changed?
c) Does it matter?
d) Should I fix it?
e) Can I fix it?
These may take weeks to answer. But these answers are the only valid ones.
Quote:
Originally posted by DaPi
But it's not! It's just one of many "tools" that you can use to judge if your db is healthy - you need to understand its usefulness and its limitations in the context of your db.
Ok, lets see how use full might be that * HIT Ratio *..
Case 1 :
Ratio > 95+
* Some argue that u gotta bad sqls where in ur LIOs shooting up so much that u cannot see the PIOs to be significant..
* But it can as well be some thing like ALL SQL perfectly tuned and ther isnt any crap requirement by which LIOs will have shoot up ... and yes now u can say HIT is perfect and u can be happy..
So with this u really cannot make anything unless u know what SQL are running and r they tuned and so many with respect to SQL.
Case 2 :
Ratio < 75-
* Some may argue that u have a balance between PIO/LIO and assume all SQL tuned one.
* Contradictory to it will be, there are very simple requirements (SQLs) and its codeded in such a fashion its worst ( BAD sqls ) and ofen reading data from files...
So with this as well u really cannot make anything unless u know what SQL are running and r they tuned and so many with respect to SQL.
So, why bother on HIT Ratio and just start looking for BAD SQLs and every one will be happy !
Abhay.
Ah, but WHEN do you look for BAD SQL? Possible answers:Quote:
Originally posted by abhaysk
So, why bother on HIT Ratio and just start looking for BAD SQLs and every one will be happy !
a) when someone complains
b) when the hit ratio changes unexpectedly
To my mind it's like a doctor using a thermometer: if the patient's temperature is normal, it doesn't necessarily mean he/she is in good health; if it is abnormal, better find out why.
On p11 of Millsap's article, he describes the hit ratio as "unreliable" - by his definition the doctor's thermometer is "unreliable" too. Would you expect doctors to stop using thermometers?
What would you think of a doctor who:
a) never used a thermometer?
b) never used anything but a thermometer?
Well the things (Ratio Vs Thermometer) u r trying relate will be meaningless...Quote:
Originally posted by DaPi
Ah, but WHEN do you look for BAD SQL? Possible answers:
a) when someone complains
b) when the hit ratio changes unexpectedly
To my mind it's like a doctor using a thermometer: if the patient's temperature is normal, it doesn't necessarily mean he/she is in good health; if it is abnormal, better find out why.
On p11 of Millsap's article, he describes the hit ratio as "unreliable" - by his definition the doctor's thermometer is "unreliable" too. Would you expect doctors to stop using thermometers?
Lets consider one by one cases..
1) Thermo shows too high value than the normal 98 F Vs Ratio Showing more than 98+
Conlusion from Thermo : Person is suerly not in good health.
Conclusion from Ration : Can u make?? ( coz it can be any may be good if all sqls running are good and no stupid requirements and also may not be good if bad sqls running )
2) Thermo shows normal value i.e 98 F Vs Ratio Showing 90 to 93.
Conlusion from Thermo : Person may or may not be in good health.
Conclusion from Ration : Can u make?? (here the ans being same).
3) Thermo shows too less value than the normal 98 F Vs Ratio Showing too less than 80 may be between (50 to 70)
Conlusion from Thermo : Person is suerly not in good health.
Conclusion from Ration : Can u make?? (here the ans being same).
Conclusion from above 3 : 2 out 3 cases for Thermo, u can be sure that something is wrong, but 0 out of 3 cases u are not sure that some thing is wrong/right ( Plainly by seeing Ratio ).
So can u realy corelate?
Yes ofcourse the second point u made ( drastic change in Hit Ratio is a very valid point, but with also u cannot be sure )..
as far as the quote
is concerned, both are CRAP !!Quote:
What would you think of a doctor who:
a) never used a thermometer?
b) never used anything but a thermometer?
I see u r big fan of * Hit Ratio * ;) ;)
Abhay.
Abhay, I think your comparison of hit ratio with temperature falls down because you know what is a "normal" temperature (actually my "normal" is 97.7 not 98.4 ! !) - for a db you need to find out what is "normal" for that db.
No not a big fan. Sometimes it's useful, so I don't call it crap.Quote:
I see u r big fan of * Hit Ratio *
Well David you are simply not willing to agree that Hit Ratio is meaningless...Quote:
Originally posted by DaPi
Abhay, I think your comparison of hit ratio with temperature falls down because you know what is a "normal" temperature (actually my "normal" is 97.7 not 98.4 ! !) - for a db you need to find out what is "normal" for that db.
Coz u know who u can decide a NORMAL temp for a person, u also know how to find normal Hit Ratio of DB ( and also know that large flucation of it is possible and not necessaryly a problem but that is not the case with thermo )...
:D :D
( i can say it be used very very rarely that to for an OLTP type system only & size of DB within TB & no no for DW/DSS )
Can someone please tell me why BAD SQL (poorly tuned) causes more LIO than PIO?
I always thought that whether or not a query used the Buffer Cache or Disk depended on whether or not the blocks were in memory. So why would a poorly written statement do this more than a well tuned one? Surely its a matter of the blocks ageing out of the buffers ...?
I (obviously) have little experience in this aspect of the job. In 4 years we've never had a user complain about poor performance so I've had little scope to justify (to management) any time spent tuning the DB. So - I'm trying to learn it theoretically and came upon some contradictory 'advice' (my original post).
The amount of LIO's practically defines what is good and what is bad SQL (relatively).Quote:
Originally posted by JMac
[B]Can someone please tell me why BAD SQL (poorly tuned) causes more LIO than PIO?
Try the following ...
Try these on your system ... same result, different LIO's.Code:set autotrace on
select owner,object_name from dba_objects
where timestamp =
(select max(timestamp) from dba_objects)
/
select owner,object_name
from
(
select owner,object_name,timestamp,
max(timestamp) over () max_timestamp
from dba_objects
)
where timestamp=max_timestamp
/
Now if this were a user query then the blocks would either be in memory or not, so the PIO's would be the same. On my little test system I got 41420 consistent gets for the first methodology and 20710 for the second -- exactly half. The cache hit ratio for second methodology is therefore half of the ratio for the first methodology.
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..Quote:
Originally posted by slimdave
You see? Better SQL = lower cache hit ratio. High cache hit ratio = potentially bad SQL.
It could be either way as well i.e
Better SQL = High cache hit ratio, lower cache hit ratio = potentially bad SQL.
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).
Yeah, but i got bored reading it.Quote:
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.
Did you run any examples where better SQL produce a high cache hit ratio?
Also, you could look at cache advice to tell you whether you would benefit fromQuote:
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).
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.
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 !Quote:
Originally posted by slimdave
Did you run any examples where better SQL produce a high cache hit ratio?
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.Quote:
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 !
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?
That's probably true about any single statistic you drag out of the db.Quote:
Originally posted by slimdave
Cache hit ratio is meaningless on it's own, right?
I think it's only available in 9i. And the DB is stuck on 8i for a few more months.Quote:
Also, you could look at cache advice ...
Interestingly, v$db_cache_advice is available in 8i, but you can't turn cache advice on, so the important columns are empty!
Jodie
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.Quote:
Originally posted by DaPi
That's probably true about any single statistic you drag out of the db.
So it's cheaper AND better to avoid these training courses :)Quote:
Originally posted by slimdave
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.
Thanks for that, I was doubting my sanity again.Quote:
Originally posted by jodie
Interestingly, v$db_cache_advice is available in 8i, but you can't turn cache advice on, so the important columns are empty!
(Is that good marketing? Advertising the next release in the SYS.V$ views?)
No Problem... I thought I was crazy, too! That's why I posted... to see if I was missing something.Quote:
Originally posted by DaPi
Thanks for that, I was doubting my sanity again.
bingo !!Quote:
Originally posted by slimdave
Cache hit ratio is meaningless on it's own, right?
I've been thinking about this, and I wonder if it is a result of students wanting "instant gratification" or a "silver bullet" or a "black or white answer". Or the teachers/writers finding it easier to give the answer.Quote:
Originally posted by slimdave
. . . . 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.
The logic might run:
1) If you could only get one statistic from your db, which would you choose? I guess I would choose the hit ratio.
2) So the hit ratio is the best.
3) We want nothing but the best.
4) We will only tune the hit ratio.
(Obviously you get the same kind of nonsense if you choose another statistic.)
I know what you mean. If you give people a set of metrics that should always be >= x on any type of Oracle database and this paves the way for "tricks and tips" on how to make sure you are hitting x or above and how you can ensure that you continue to do so. People like these kind of books and courses (and I admit I used to be one a couple of years ago). You think that for a small fee you will know everything you need to make any database run efficiently! Each new tuning book seems to be the same as the last e.g. a collection of "hot" SQL scripts, and a cottage industry in born. This seems to be changing now though, what with Milsap and Holt's new book. I reckon I've learnt more from reading that than I have from reading most of the big Oracle Press books combined!Quote:
I've been thinking about this, and I wonder if it is a result of students wanting "instant gratification" or a "silver bullet" or a "black or white answer". Or the teachers/writers finding it easier to give the answer.
I went to Oracle Technology Day yesterday and throughout the whole seminar I kept hearing:
"Check your cache hit ratio"
"You can monitor your buffer cache hit ratio"
"This allows you to easily tune your db according to your buffer cache hit ratio"
Even Oracle tries to pound this into everyone's heads.
Maybe they can't think of any other power words besides those ...
I will be glad if 10g's automatic tuning is based on hit ratio... will keep my job LOL
What if you chose volume of PIO's, or duration of PIO waits? I think I'd be happier with that.Quote:
Originally posted by DaPi
(Obviously you get the same kind of nonsense if you choose another statistic.)
Yes, better - but still silly to focus on just one statistic.Quote:
Originally posted by slimdave
What if you chose volume of PIO's, or duration of PIO waits? I think I'd be happier with that.
If you blindly minimise PIO you might end up back where we started with Millsap's LIO/PIO ratio of 99.99999999 . . . .
I can't say I've ever seen that in my Oracle applications - but I did have a problem with Rdb under VMS: version 1 would thrash the disks for 5 minutes then the CPU would go to 100% and stay there for days (I'd cancel it on Monday morning); I could rewrite it as version 2, which would churn the disks for 30 minutes an then give the result (I think the optimiser was sensitive to litterary style!).
I suspect that version 2 did more PIO, but I had neither the tools nor the expertise to prove it.
There is no silver bullet. Over and out.
