SQL Tuning   «Prev  Next»

Lesson 5The EXPLAIN PLAN utility
ObjectiveUnderstand the Basic Functions of the Oracle EXPLAIN PLAN Utility

Oracle EXPLAIN PLAN Utility

Basic Functions

View access path to data

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.
The first section deletes all rows from the plan table
1) The first section deletes all rows from the plan table 
set echo off
set verify off 
delete from plan_table;
commit

2) The main part of the SQL populates the plan table with the access path information
2) The main part of the SQL populates the plan table with the access path information.

3) The final step displays the access information from the plan table. 
set echo off
select
lpad(' ', 2*(level-1)) ||
operation ||'  '||
decode(id, 0, 'Cost = '||position) "OPERATION",
options, object_name
from plan_table
START WITH id=0
connect by prior id= parent_id;
The final step displays the access information from the plan table.

4) The query produces the following output. Here we see the actual access path to the data
4) The query produces the following output. Here we see the actual access path to the data. We will bring re-visiting this topic in detail in a later lesson to expand your understanding of outputs like the one above.

Oracle Explain Plan Utility

Understanding the Execution Plan

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.

First Utility

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.

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

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