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.