Previous Table of Contents Next


Using EXPLAIN PLAN

The EXPLAIN PLAN command shows you the execution plan the Oracle optimizer has chosen for your SQL statements. With this information, you can determine whether the Oracle optimizer has chosen the correct execution plan based on your knowledge of the data and the application. You can also use EXPLAIN PLAN to determine whether any additional optimization should be done to your database (for example, the addition of an index or the use of a cluster).

The EXPLAIN PLAN command is used to display the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. After using EXPLAIN PLAN, you can rewrite your SQL statements and see whether the new SQL statement is better optimized than the original statement. By analyzing the output, you may be able to provide hints the Oracle optimizer can use to better take advantage of the data. By using hints, you can take better advantage of features such as the Oracle Parallel Query option.

EXPLAIN PLAN Initialization

When you run SQL statements with the EXPLAIN PLAN command, the output of EXPLAIN PLAN is put into a table with the default name plan_table. You must create this table before you can run EXPLAIN PLAN. The table can be created in one of two ways:

  Use the UTLXPLAN.SQL script provided by Oracle.
  Create the plan_table table by hand.

The plan_table table is defined as follows:

SQL> describe plan_table
 Name                            Null?    Type
 ------------------------------- -------- ----
 STATEMENT_ID                             VARCHAR2(30)
 TIMESTAMP                                DATE
 REMARKS                                  VARCHAR2(80)
 OPERATION                                VARCHAR2(30)
 OPTIONS                                  VARCHAR2(30)
 OBJECT_NODE                              VARCHAR2(128)
 OBJECT_OWNER                             VARCHAR2(30)
 OBJECT_NAME                              VARCHAR2(30)
 OBJECT_INSTANCE                          NUMBER(38)
 OBJECT_TYPE                              VARCHAR2(30)
 OPTIMIZER                                VARCHAR2(255)
 SEARCH_COLUMNS                           NUMBER
 ID                                       NUMBER(38)
 PARENT_ID                                NUMBER(38)
 POSITION                                 NUMBER(38)
 COST                                     NUMBER(38)
 CARDINALITY                              NUMBER(38)
 BYTES                                    NUMBER(38)
 OTHER_TAG                                VARCHAR2(255)
 PARTITION_START                          VARCHAR2(255)
 PARTITION_STOP                           VARCHAR2(255)
 PARTITION_ID                             NUMBER(38)
 OTHER                                    LONG

SQL>

You do not have to name the table plan_table. You can direct EXPLAIN PLAN to use a table of another name if you want.

Invoking EXPLAIN PLAN

Invoke the EXPLAIN PLAN command with the following Oracle command sequence:

EXPLAIN PLAN
    SET STATEMENT_ID = ‘myplan’
    INTO plan_table
    FOR
        SQL Statement;

STATEMENT_ID should reflect the statement’s function so you can recognize it at a later time. The plan_table parameter is the name of the table you created as described in the preceding section. If the INTO clause is omitted, the command defaults to the name plan_table.

Here is an example of a completed command:

SQL> EXPLAIN PLAN
  2  SET statement_id = 'myplan’
  3  FOR
  4  SELECT *
  5  FROM perftable, perftable2, perftable3
  6  WHERE perftable.id1 = perftable2.id1
  7  AND perftable.id2 > 5000
  8  AND perftable3.id1 > 100 AND perftable3.id1 < 400
  9  ORDER BY perftable.id1;

Explained.

The results of the EXPLAIN PLAN are written into the table plan_table. The following section explains how to retrieve the information in that table.

Extracting EXPLAIN PLAN Results

The output of EXPLAIN PLAN is written to the table specified in the EXPLAIN PLAN command (by default, to the table named plan_table). You must extract this information in order to look at the results of EXPLAIN PLAN. The results can be displayed with a query such as this:

SELECT SUBSTR(LPAD(' ',2*(LEVEL-1))||operation,1,30)
||' '||SUBSTR(options,1,15)
||' '||SUBSTR(object_name,1,15)
||' '||SUBSTR(DECODE(id, 0, 'Cost = '||position),1,12)
“Statement Execution Plan”,
SUBSTR(optimizer, 1, 10) “Optimizer”
FROM
    plan_table
START WITH
    id = 0 AND statement_id = 'myplan’
CONNECT BY PRIOR
    id = parent_id
AND
    statement_id = 'myplan’;

This query results in the following output:

Statement Execution Plan
Optimizer
---------------------------------------------------------------- --------
SELECT STATEMENT   Cost = 25012                                   CHOOSE
  MERGE JOIN
    SORT JOIN
      MERGE JOIN CARTESIAN
        TABLE ACCESS FULL PERFTABLE
        SORT JOIN
          TABLE ACCESS FULL PERFTABLE3
ANALYZED
    SORT JOIN
      TABLE ACCESS FULL PERFTABLE2

9 rows selected.

If the optimizer had chosen a cost-based approach, the cost of the query would have been reflected in the first line of the optimization plan. Any features such as parallel query are also reflected here.

With this information, you can tell whether your SQL statements take advantage of indexes, clusters, or hash clusters. If you use EXPLAIN PLAN, you can see precisely how your SQL statement is being executed and what effect any changes you make to the SQL statements have on the execution plan. Changing your SQL statements to take advantage of an index or a cluster, for example, will show an immediate improvement. EXPLAIN PLAN output is ideal for pointing out your execution plan and may indicate that where you thought that you were taking advantage of an index, you actually were not.


Previous Table of Contents Next