I agree wholeheartedly with your “no such thing as an ‘overall system’” comment. That is key. For the record, my view on this general topic includes the following observations:
* If you have a really high database buffer cache hit ratio (>99%), then you almost certainly have inefficient SQL in your application.
* From the hundreds of situations we’ve seen in the past two years (we help people analyze at least one Hotsos Profiler run per workday), not one single site would have improved their performance noticeably by caching their entire database in memory. (It’s Amdahl’s Law: not one performance problem we’ve seen has been caused by too much time spent waiting for PIOs.)
* Nearly every slow application we’ve seen since 1999 either spends the preponderance of its elapsed time doing too many LIO calls (which manifests itself as an apparent “CPU problem” or a “latch contention problem”), or too many inter-tier database calls (which manifests itself as an apparent “network problem”).
* The solution for 99%+ of performance problems I have ever seen or heard of is to reduce LIO count (fix SQL), or database call count (fix application code). The key is to figure out *which* SQL, *which* application code, and *what* to go do about it.
Cary Millsap -----Original Message-----
Well, I guess that I disagree. Buffer hit radio does matter as one of the performance indicators, but certainly not the only one. Your and Mr. Milsap thesis is that LIO also is very expensive and its cost is far from being negligible, so having gazillion of LIOs instead of 100 times smaller number of PIOs will not make our system run faster. BHR alone cannot be used to judge to overall health of the system, but thebn again, there is no such thing as the "overall health of the system". It's the users of the system who will say whether the performance is satisfactory or not, and I'm usually tuning an application, not an imaginary "overall system". Low cache hit ratio usually tells me that I do have a hog who is using lots of PIOs. By my experience, it usually is a very good indicator that something is wrong, at least on an OLTP system. So, after all, I do find BHR a useful indicator, but by no means the only one or the most important one. Event 10046, SQL_TRACE (level 1 of 10046), explain plan and v$session_event still are the tools I need most, but I still do need BHR as an indicator. Mladen
Gogala
|
Title: RE: missed Anjo's webcast..
- RE: missed Anjo's webcast.. Gogala, Mladen
- RE: missed Anjo's webcast.. Rajesh . Rao
- RE: missed Anjo's webcast.. Post, Ethan
- RE: missed Anjo's webcast.. MacGregor, Ian A.
- RE: missed Anjo's webcast.. Cary Millsap
- RE: missed Anjo's webcast.. Jesse, Rich
- Re: missed Anjo's webcast.. Mladen Gogala
- RE: missed Anjo's webcast.. MacGregor, Ian A.
- RE: missed Anjo's webcast.. Jared . Still
- RE: missed Anjo's webcast.. Freeman, Robert
- RE: missed Anjo's webcast.. Cary Millsap
- RE: missed Anjo's webcast.. Connor McDonald
- Re: missed Anjo's webcast.. Anjo Kolk
- Re: missed Anjo's webcast.. Anjo Kolk
- Re:RE: missed Anjo's webcast.. dgoulet