SQL Tuning

Foreword

During my years as a developer, and later as a database administrator, the only performance tuning area in which I developed any expertise and had any level of success was that of tuning individual SQL statements. Because of that background, I was very interested when Dan Tow came to me with his idea for a book on SQL tuning.

The problem with SQL tuning, at least from my own perspective, is that it was often reasonably easy to pinpoint badly performing SQL statements that needed to be tuned, and it was reasonably easy to determine the execution plan currently being used for those badly performing statements, but then came the hard problem: finding a better execution plan, to make an offending SQL statement execute faster. Many is the time I’ve stared at a poorly performing SQL statement, reviewing its poorly performing execution plan, and wondered just what I should do next. For that matter, was there even any room for improvement? Perhaps the execution plan that performed so badly from a user’s perspective was, in fact, the best possible execution plan. Perhaps I was wasting my time trying to guess at a better one.

There, I used that word guess, and that’s at the heart of what sometimes made tuning SQL statements a frustrating activity. It all came down to my looking at a SQL statement and trying to guess at a better plan. Of course, I’d attempt to factor in my experience, my intuition, and my knowledge of the data being queried, and I’d pull out tips and tricks that I’d read about in books and magazine articles, but in the end I’d make a guess, try a new plan, make a guess, try a new plan, and so forth. I’d stop when one of two things happened:

• I got lucky and guessed a plan that was a good-enough improvement over the old plan to satisfy my client.

• I ran out of ideas.

It always bothered me when I ran out of ideas, because I never knew for sure whether the current plan really was optimal or whether I was just too thick-headed to intuit a better-performing execution plan than the current one. To be honest, I was always pretty hard on myself, chalking up any failure to improve a SQL statement as a personal inadequacy.

Dan doesn’t guess. Let me boldface that and underline it. He doesn’t execute an iterative guess-loop like I did, trying one idea after another in the hope of stumbling across an improvement. Instead, Dan uses an innovative and mathematically based diagramming method to derive the optimal, or near-optimal, execution plan for a SQL statement. Then he puts that plan into effect. And that’s it. There’s no guesswork, and there’s no uncertainty as to whether further improvement is possible.

At first, I was skeptical of Dan’s approach. But the more I read his chapters, the more I began to see the logic behind his method. Math doesn’t lie, and experience is a good indicator. Dan has over 10 years of tuning experience, and he’s been very successful using the method described in this book.

There are three legs that any SQL tuning effort needs to stand on. You need to know how to identify a badly performing SQL statement. You then need to be able to see the execution plan used for that statement. Finally, you need to somehow come up with an improved plan. I’m convinced that Dan’s method represents a viable third leg on which your SQL tuning efforts can rest. Read his book, apply his method, and save yourself hours of wondering what to do next.

—Jonathan Gennick, author, editor, Oracle DBA