I’ve pointed out in the past how bad the Instance Efficiency ratios are in highlighting a performance problem. Here’s a recent example from OTN repeating the point. The question, paraphrased, was:
After going through AWR reports (Instance Efficiency Percentages) I observed they have low Execute to Parse % but high Soft Parse %.
Please share if you had faced such issue and any suggestions to solve this
From the Oracle-L mailing list in the last 48 hours:
I am working on tuning an app running against oracle 10.2.0.3 We have 48G on the server; my db_cache is 18G. When I look at the awr reports, I see db hit ratio being over 99% and a lot of waits for db sequential reads. Based on the SQL there are a lot of table reads based on the primary keys so that kind of waits is reasonable. But the question is if the hit ratio is that high , if we read mostly for the cache, why do we do that many reads. Is there an explanation for that?
A little while ago I wrote some notes on why monitoring the buffer cache hit ratio (BCHR) was a pointless exercise. I’ve recently come across a lovely example that demonstrates very clearly the point that Connor McDonald was trying to make in his ‘pick a hit ratio’ script.
I thought that most people had managed to get over the buffer cache hit ratio (BCHR) as an aid to monitoring an Oracle system, so I was a little surprised to see a recent question on comp.databases.oracle.server about how to calculate it “properly” turning into a rather long thread – about 120 posts to date.
The consensus was that there was no meaningful way in which the BCHR could be used to monitor an Oracle system.
A recent post on comp.databases.oracle.server asked the question “why does v$sysstat give you a different hit ratio from v$buffer_pool_statistics even when you have just one buffer pool for the instance”.
The largest part of the answer is because very few people have bothered to think carefully about what the “buffer cache hit ratio” means, and which statistics are relevant. If you are running 10g, though, you get a few clues about some of the issues involved.
Theme: Rubric. Blog at WordPress.com.
