Explain Plan

EXPLAIN PLAN parses a query and records the "plan" that Oracle devises to execute it. By examining this plan, you can find out if Oracle is picking the right indexes and joining your tables in the most efficient manner. There are a few different ways to utilize Explain Plan. We will focus on using it through SQL*Plus since most Oracle programmers have access to SQL*Plus.

Creating a Plan Table

The first thing you will need to do is make sure you have a table called PLAN_TABLE available in your schema. The following script will create it for you if you don't have it already:

@?/rdbms/admin/utlxplan.sql

Explain Plan Syntax

 EXPLAIN PLAN FOR your-sql-statement;

or

 EXPLAIN PLAN SET STATEMENT_ID = statement_id FOR your-sql-statement;

Formatting the output

After running EXPLAIN PLAN, Oracle populates the PLAN_TABLE table with data that needs to be formatted to presented to the user in a more readable format. Several scripts exist for this, however, one of the easiest methods available is to cast dbms_xplan.display to a table and select from it (see examples below).

Some Examples

SQL> EXPLAIN PLAN FOR select * from dept where deptno = 40;
Explained.

SQL> set linesize 132
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2852011669
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    20 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=40)

14 rows selected.

Using SQL*Plus Autotrace

SQL*Plus also offers an AUTOTRACE facility that will display the query plan and execution statistics as each query executes. Example:

SQL> SET AUTOTRACE ON
SQL> select * from dept where deptno = 40;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

Execution Plan
----------------------------------------------------------
Plan hash value: 2852011669

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    20 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=40)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        443  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Also see

• DBMS_XPLAN - Package to format and display SQL Query Plans