Oracle EXPLAIN PLAN Utility

More than any other tool, the EXPLAIN PLAN utility is the most indispensable. In fact, all of the other Oracle tools rely on the EXPLAIN PLAN utility to allow the DBA to view the access path to the data. We have no hope of tuning a SQL query until we can see how it is accessing the database, and the EXPLAIN PLAN utility makes the access path visible. The EXPLAIN PLAN utility can be invoked directly on the UNIX server with the EXPLAIN PLAN command syntax, or it can be invoked from Oracle SQL analyze client software.
As we noted in an earlier lesson, it is never clear from looking at an SQL statement how the database will be accessed. To illustrate, consider the following SQL statement presented in the Slide Show.

Oracle's query optimizer uses an execution plan for each query submitted. By and large, although the optimizer does a good job of selecting the execution plan, there may be times when the performance of the database suggests that it is 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 2-5.

Oracle Explain Plan Utility
We will now take a deeper look into SQL tuning by examining the optimizer modes.