O'Reilly logo

Optimizing Oracle Performance by Jeff Holt, Cary Millsap

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Appendix B. Optimizing Your Database Buffer Cache Hit Ratio

Shortly after I joined Oracle Corporation in 1989, several of my technical mentors taught me that just about the only thing you could tell from looking at a database’s buffer cache hit ratio is that when it’s really high, it’s usually a sign of trouble [Millsap (2001b)]. In the several years that have passed since my first exposure to that lesson, the battle has raged between advocates of using the buffer cache hit ratio as a primary indicator of performance quality and those who believe that hit ratio metrics is too unreliable for such use. It’s not been much of a battle, actually. The evidence that hit ratios are unreliable is overwhelming, and similar ratio fallacies occurring in other industries are well documented (see, for example, [Jain (1991)] and [Goldratt (1992)]).

One of the most compelling (and funniest) proofs that hit ratios are unreliable is a PL/SQL procedure called choose_a_hit_ratio written by Connor McDonald. Connor’s procedure lets you increase your database buffer cache hit ratio to any value that you like between its current value and 99.999 999 9%. How does it work? By adding wasteful workload to your system. That’s right. You specify what you want your database buffer cache hit ratio to be, and choose_a_hit_ratio adds just enough wasteful workload to raise your hit ratio to that value. What you get in return is proof positive that having a high database buffer cache hit ratio is no indication that ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required