Optimizing Oracle Performance

Foreword

Seldom does an editor have the privilege of editing a book such as this one. From the moment I first saw Cary Millsap’s and Jeff Holt’s proposal for a book called Optimizing Oracle Response Time (that was the original working title) I knew I’d struck gold. This book has everything an editor dreams of: talented authors, rigorous research, groundbreaking material.

I remember well my first foray into Oracle performance tuning. It was back in the Oracle7 days when I was becoming grounded in Oracle. I’d just been handed DBA responsibility for all the databases used by my development group, and what better way to begin my career as an Oracle DBA, I thought, than to make an impact via some serious tuning? So I bought a book. And I read the Oracle manual. I learned about the buffer cache, the shared pool, and hit-ratios.

It all seemed so simple. The previous DBA had never done any tuning, so all I needed to do was tweak a few parameter settings, use hit-ratios to find the optimum memory allocations for the buffer cache and shared pool, and then I could kick back, bask in the glory of a job well done, and drink in the praises from my fellow developers who would no doubt be awed at how fast I could make their programs run.

Only it didn’t work the way I’d envisioned. I doubled the size of my buffer cache, but nothing seemed to run any faster. I cut my buffer cache in half from its original size, but nothing seemed to run any slower. I increased the shared pool. I decreased the shared pool. I tinkered with this parameter and that parameter, and always the database stubbornly seemed to run on at the same speed as ever. Obviously this tuning business was more difficult than I’d envisioned, and I just wasn’t smart enough to “get it.” Humbled, I set aside my dreams of becoming a crackerjack performance tuner.

Fortunately, for my ego and probably for my career as well, I eventually discovered SQL trace, and learned how to generate trace files and use tkprof to report on their contents. I was no good at tuning the database as a whole, I decided, but I did become reasonably good at using SQL trace to identify and fix badly performing SQL statements.

Eventually, I came to the conclusion that what really mattered was what users were complaining about. Users don’t care about hit-ratios, nor about disk-throughput; they don’t care whether latch contention exists, nor do they care about any other arcane statistic that you or a system administrator might think to worry about. Users only care about one statistic: how fast their jobs run. For the most part, I gave up worrying about anything other than what my clients were worrying about. When a client complained about slow performance, I measured my success not by any arcane ratio or statistic, but by how much time I could save them. Still, I was often haunted by thoughts that I was missing something about this tuning business, that there was some level to which I just wasn’t capable of ascending. Real DBAs, I felt, monitored statistics such as hit-ratios to keep up with the overall performance of their databases. Why couldn’t I make that work?

Cary’s and Jeff’s proposal, my conversations with them, their class which I attended, and this book freed me from the vestiges of guilt and shame over my lack of success tuning databases at the instance level using hit-ratios and other instance-wide statistics. I’d learned to focus on user response time; Cary and Jeff validate that. I’d become frustrated, indeed I felt belittled, at my inability to manage performance by monitoring hit-ratios and other instance-wide statistics; Cary and Jeff throw hit-ratios out the window. My greatest tuning successes came from using trace file statistics (SQL trace and tkprof) from a specific job to determine what SQL statements were consuming the greatest time; Cary and Jeff take the use of trace file statistics to a whole new level.

By now you can see why I was, and am, so excited by this book. Everything about Cary’s and Jeff’s method resonates with my own experience. As I talked with them, every time they brought up a new point I found myself nodding my head thinking: “yes! yes! of course!” When they first talked to me about their vision for this book, they were “preaching to the choir,” though they probably didn’t know it at the time. What’s so exciting is that Cary and Jeff saw clearly where I saw only dimly. There is indeed a level above where I was at, but it’s one that I am capable of ascending to, and it’s one you too can reach with Cary and Jeff’s help.

After talking with Cary and Jeff about their plans for a book, I knew I had to have it as an O’Reilly publication. I thoroughly enjoyed editing their work. I learned a lot from reading the chapters over and over again, and I know you will too. I can’t recommend this book strongly enough. I’m glad you bought it; you won’t regret the investment. And if you’re reading this Foreword in a bookstore aisle, please, don’t put the book back on the shelf. Invest the price of this book in your own development. You’ll reap returns many times over. I’m sure of it.

—Jonathan Gennick