Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Understanding the Execution Plan

Oracle’s query optimizer automatically selects an execution plan for each query submitted. By and large, although both types of optimizer do a good job of selecting the execution plan, there may be times when the performance of the database suggests that it’s using a less-than-optimal execution plan.

The only way you can really tell what path is being selected by the optimizer is to see the layout of the execution plan. You can use two Oracle character-mode utilities to examine the execution plan chosen by the Oracle optimizer. These tools allow you to see the successive steps used by Oracle to collect, select, and return the data to the user.

The first utility is the SQL EXPLAIN PLAN statement. When you use EXPLAIN PLAN, followed by the keyword FOR and the SQL statement whose execution plan you want to view, the Oracle cost-based optimizer returns a description of the execution plan it will use for the SQL statement and inserts this description into a database table. You can subsequently run a query on that table to get the execution plan, as shown in SQL*Plus in Figure 4-5.

Figure 4-5. Results of a simple EXPLAIN PLAN statement in SQL*Plus

The execution plan is presented as a series of rows in the table, one for each step taken by Oracle in the process of executing the SQL statement. The optimizer also includes some of the information related to its ...