SQL Tuning

Chapter 3. Viewing and Interpreting Execution Plans

Set the cart before the horse.

—John Heywood Proverbs [1546], Pt. I, Ch. 7

This chapter covers basic material about generating and reading execution plans. It’s optional, in terms of both when and whether you need to read it for the rest of the book to make sense. The database vendors all provide specialized, often graphical tools to generate and view execution plans. There are also popular third-party tools, such as TOAD, for this purpose. If you have access to these well-documented tools and already know how to use them, you can probably skip or skim this chapter. Otherwise, this chapter is not intended to replace or compete with specialized tools or their documentation. Instead, I describe the most basic methods of generating and reading execution plans, methods that are guaranteed to be available to you regardless of the available tools in your environment. These basic methods are especially useful to know if you work in diverse environments, where you cannot count on having the specialized tools readily available. If you already have and use more elaborate tools, you won’t need (and might not even like) my methods. In my own work, across diverse environments, I never bother with the more elaborate tools. I have found that when you know which execution plan you want and how to get it, simple tools, native to the database, will suffice. Reading an execution plan is just a quick check for whether the database is using the desired ...