Why Are Cache-Hit Ratios Grossly Inefficient?

The hit ratio philosophy is not peculiar to Oracle database administration. It is widely used and ingrained in many aspects of our daily lives. Take your local city water department for example. Their goal is to keep your drinking water quality within the limits that are established by the Environmental Protection Agency. Now, your city may meet or exceed the established limits, but that doesn ‚ t mean that everyone is happy. Some people are more vulnerable to substances found in drinking water than the general population. And if you are one of the unfortunate ones, you have to work out your own alternative. Chances are your city is not going to do anything about it if the water quality is already within the acceptable limits.

Like it or not, we gave the same kind of customer service to our database users. If they said their job ran slow, all we did was make sure all the ratios were in line. Among them was the buffer cache hit ratio, which was the center of attention. If the ratios were in line, we sent our users away, telling them that they really didn ‚ t have a problem. If they continued to complain, we would turn SQL trace on for the job, examine the trace file with tkprof (Transient Kernel Profiler), and tune some SQL statements. What else could we do? All the performance tuning books and classes taught us that if the ratios were within their acceptable limits, users should be happy. This kind of service is no different than a doctor who only knows how to treat patients based on their blood pressure numbers . Suppose you slit your wrist, bleed profusely, and squirm in pain. The doctor measures your blood pressure, finds it to be in the acceptable range, and sends you away asking you to come back again when your blood pressure is lower.

If the ratio numbers weren ‚ t in line, the common (and perhaps the only) solution was to add more memory. Those of you who have been there and done that can certainly remember the painful lesson that a larger buffer cache does not always translate to better performance. Remember when the additional memory was installed and you crossed all your fingers and toes, hoping and praying that performance would get better? Alas, the performance didn ‚ t get better, it got worse . Surprised and disbelieving, you were totally lost and didn ‚ t know what else to do. You lost your credibility and you were in the hot seat. In the follow-up meeting with your customer, someone suggested, ‚“Why don ‚ t we bring in the industry-renowned performance tuning expert? ‚½ Of course, your boss was also present in the meeting. You felt so small and wished for a place to hide. The expensive expert came in and performed an analysis of the database while you took a back seat. The expert produced a fancy multi-page, multi-colored report with charts and graphs that dazzled your boss and user and left. Regardless of whether the recommended solution worked or not, your reputation was already damaged. Your tuning methodology let you down and you became a ‚“tuning-phobic. ‚½

Ratio numbers were the only form of communication in those days. We had to communicate performance using a few numbers, especially the buffer cache hit ratio, and it wasn ‚ t easy. We always wondered why we got blank stares and glazes in our users ‚ eyes when we told them that the database cache hit ratio was high and totally ignored their complaint about response time being very slow. Obviously, our language was foreign to them, but we couldn ‚ t comprehend why they couldn ‚ t comprehend. I mean, how hard could it be? High cache hit ratio is good, and you shouldn ‚ t have a problem. Low cache hit ratio is bad, and you should expect problems, right? But ratio numbers don ‚ t really make sense to those who want to know why their job ran so slowly. Imagine calling to find out why your pizza delivery is running late and being told the driver ‚ s hit-rate ratio is 65 percent. Or suppose your boss asks why you were late to work. You would probably attribute your tardiness to one or more events, such as an accident , treacherous driving conditions, slow traffic, traffic lights, and so on. Chances are you wouldn ‚ t explain your tardiness to your boss in some sort of ratio.

The cache-hit ratio method is simply not reliable for performance analysis and tuning, or else Oracle Corporation wouldn ‚ t have introduced a new methodology. The doctrine of high cache-hit ratio for good performance is wrong most of the time. In reality, high cache-hit ratio doesn ‚ t always mean good performance, and low cache-hit ratio doesn ‚ t always mean bad performance. In fact, there were many times when cache-hit ratios hit the floor, yet there was no loss in application performance. Trying to judge performance by a cache-ratio number is like trying to determine the speed of a car by its tachometer reading. You can ‚ t tell how fast someone is going at 6000 rpm without the speedometer , can you? The tachometer reading is high, but the car may be in second gear.

The Oracle Database 10 g Release 1 Performance Tuning Guide states,

When tuning, it is common to compute a ratio that helps determine whether there is a problem. Such ratios include the buffer cache-hit ratio, the soft-parse ratio, and the latch-hit ratio. These ratios should not be used as ‚hard and fast ‚ identifiers of whether there is or is not a performance bottleneck. Rather, they should be used as indicators. In order to identify whether there is a bottleneck, other related evidence should be examined.