SQL Tuning [Electronic resources] نسخه متنی

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 plan.

If you choose to read this chapter, you can probably skip straight to
the section on reading execution plans for your choice of vendor
database, unless you want to tune on multiple vendor databases. Each
of those sections stands alone, even repeating material from the
other sections, when applicable. However, as you read this chapter,
please keep in mind that the execution plans you see will not really
be useful to you until you have learned the material of Chapter 5-Chapter 7. These
later chapters will teach you how to decide which execution plan you
even want, and viewing execution plans is of little use unless you
know which plan you want.