EXPLAIN PLAN   «Prev  Next»

Lesson 4 The EXPLAIN PLAN utility
Objective Run an EXPLAIN PLAN statement.

Oracle EXPLAIN PLAN Utility

Creating Path Information

Running an EXPLAIN PLAN is a fairly straightforward process.
  1. After the PLAN TABLE has been created, we must populate it with access path information by running the SQL statement prefaced with the following:
    EXPLAIN PLAN SET STATEMENT_ID = ‘test1’ 
    FOR <<ADD YOUR SQL HERE >>
    
  2. Once the access path information has been created, we run the plan.sql script, and analyze the output.

The following SlideShow will demonstrate the process.


  1. We create the PLAN TABLE by running the utlxplan.sql script,
  2. Once the PLAN TABLE has been created, we must populate it with access path information.
  3. At this point, the SQL optimizer has only been directed to compute the access place and place it in the PLAN TABLE.
  4. The EXPLAIN PLAN output, if read correctly, reveals vital information.
Create Plan Table
Now that we have a broader understating of how to use the EXPLAIN PLAN, let us try out our new skills by seeing how to detect full-table scans.

Reading Oracle Plan Table

Before moving on to the next lesson, click the Exercise link to try your hand at creating a PLAN table and analyzing an output.
Reading Oracle Plan Table