Database Reference
In-Depth Information
Chapter 10
Execution Plans
An
execution plan
describes the operations carried out by the engine to execute a SQL statement. Every time you have
to analyze a performance problem related to a SQL statement, or simply question the decisions taken by the query
optimizer, you must know the execution plan. Without it, you're like a blind man with his cane in the middle of the
Sahara Desert, groping around trying to find his way. I can't stress enough that the first thing to do while analyzing or
questioning the performance of a SQL statement is to get its execution plan.
Whenever you deal with an execution plan, you carry out three basic actions: you obtain it, you interpret it, and
you judge its efficiency. The aim of this chapter is to describe in detail how you should perform these three actions.
Obtaining Execution Plans
Basically, Oracle Database provides five methods to obtain the execution plan associated with a SQL statement:
EXPLAIN PLAN
statement and then query the table where the output was written.
•
Execute the
•
Query a dynamic performance view showing the execution plans cached in the library cache.
•
Use Real-time Monitoring to get information about SQL statements being executed or that just
completed.
•
Query an Automatic Workload Repository (AWR) or Statspack table, showing the execution
plans stored in the repository.
•
Activate a tracing facility providing execution plans.
Even though there are other methods of obtaining execution plans (for example, as described in Chapter 11, with
features related to SQL profiles and SQL plan baselines), those methods can't be used to directly obtain the execution
plan associated with a given SQL statement. Hence, they're not covered in this chapter. Because all tools displaying
execution plans take advantage of one of the five methods just listed, the following sections describe just the basics
rather than focus on specific tools such as Oracle Enterprise Manager, PL/SQL Developer, or Toad. I don't discuss
such tools here also because, more often than not, they don't provide all the information you need for a thorough
analysis. Note that Real-time Monitoring is described in Chapter 4.
The EXPLAIN PLAN Statement
The aim of the
EXPLAIN PLAN
statement is to take a SQL statement as input and provide its execution plan and some
information related to it as output in the
plan table
. In other words, with it you can ask the query optimizer which
execution plan would be used to execute a given SQL statement.
