Oracle7 Server Tuning

Library

Product

Contents

Index

Output Table Columns

• The EXPLAIN PLAN Command
• Introduction
• Creating the Output Table
• Output Table Columns
• Formatting EXPLAIN PLAN Output

The EXPLAIN PLAN Command

This chapter shows how to use the SQL command EXPLAIN PLAN.
It covers the following topics:

• Introduction

• Creating the Output Table

• Output Table Columns

• Formatting EXPLAIN PLAN Output

Introduction

EXPLAIN PLAN results alone cannot tell you which statements will perform well, and which badly. For example, just because EXPLAIN PLAN indicates that a statement will use an index does not mean that the statement will run quickly. The index might be very inefficient! Use EXPLAIN PLAN to determine the access plan, and to test modifications to improve the performance.

It is not necessarily useful to subjectively evaluate the plan for a statement, and decide to tune it based only on the execution plan. Instead, you should examine the statement's actual resource consumption. For best results, use the Oracle Trace or SQL trace facility and TKPROF to examine performance information on individual SQL statements.

Attention: EXPLAIN PLAN tells you the execution plan the optimizer would choose if it were to produce an execution plan for a SQL statement at the current time, with the current set of initialization and session parameters. But this is not necessarily the same as the plan that was used at the time the given statement was actually executed. The optimizer bases its analysis on many pieces of data--some of which may have changed! Furthermore, because the behavior of the optimizer is likely to evolve between releases of the Oracle Server, output from the EXPLAIN PLAN command will also evolve. Such changes will be documented as they arise.

The row source count values appearing in EXPLAIN PLAN identify the number of rows that have been processed by each step in the plan. This can help you to identify where the inefficiency in the query lies (that is, the row source with an access plan that is performing inefficient operations).k

Creating the Output Table

• Run the SQL script UTLXPLAN.SQL to create a sample output table called PLAN_TABLE in your schema. The exact name and location of this script may vary depending on your operating system. PLAN_TABLE is the default table into which the EXPLAIN PLAN statement inserts rows describing execution plans.

• Issue a CREATE TABLE statement to create an output table with any name you choose. When you issue an EXPLAIN PLAN statement you can direct its output to this table.

CREATE TABLE plan_table 
    (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  NUMERIC,
    object_type      VARCHAR2(30),
    optimizer        VARCHAR2(255),
    search_columns   NUMERIC,
    id               NUMERIC,
    parent_id        NUMERIC,
    position         NUMERIC,
    cost             NUMERIC,
    cardinality      NUMERIC,
    bytes            NUMERIC,
    other_tag        VARCHAR2(255)
     other            LONG);

Output Table Columns

The following table describes the values which may appear in the OTHER_TAG column.

OTHER_TAG Text	Interpretation
(blank)	serial execution
serial_from_remote	serial execution at a remote site
serial_to_parallel	serial execution, output of step is partitioned or broadcast to parallel query servers
parallel_to_parallel	parallel execution, output of step is repartitioned to second set of parallel query servers
parallel_to_serial	parallel execution, output of step is returned to serial "query coordinator" process
parallel_combined_with_parent	parallel execution, output of step goes to next step in same parallel process. No interprocess communication to parent
parallel_combined_with_child	parallel execution, input of step comes from prior step in same parallel process. No interprocess communication from child

The following table lists each combination of OPERATION and OPTION values produced by the EXPLAIN PLAN command and its meaning within an execution plan.

OPERATION	OPTION	Description
AND-EQUAL		An operation that accepts multiple sets of ROWIDS and returns the intersection of the sets, eliminating duplicates. This operation is used for the single-column indexes access path.
CONNECT BY		A retrieval of rows in a hierarchical order for a query containing a CONNECT BY clause.
CONCATENATION		An operation that accepts multiple sets of rows and returns the union-all of the sets.
COUNT		An operation that counts the number of rows selected from a table.
	STOPKEY	A count operation where the number of rows returned is limited by the ROWNUM expression in the WHERE clause.
FILTER		An operation that accepts a set of rows, eliminates some of them, and returns the rest.
FIRST ROW		A retrieval on only the first row selected by a query.
FOR UPDATE		An operation that retrieves and locks the rows selected by a query containing a FOR UPDATE clause.
INDEX*	UNIQUE SCAN	A retrieval of a single ROWID from an index.
	RANGE SCAN	A retrieval of one or more ROWIDs from an index. Indexed values are scanned in ascending order.
	RANGE SCAN DESCENDING	A retrieval of one or more ROWIDs from an index. Indexed values are scanned in descending order.
INTERSECTION		An operation that accepts two sets of rows and returns the intersection of the sets, eliminating duplicates.
MERGE JOIN+		An operation that accepts two sets of rows, each sorted by a specific value, combines each row from one set with the matching rows from the other, and returns the result.
	OUTER	A merge join operation to perform an outer join statement.
CONNECT BY		A retrieval of rows in hierarchical order for a query containing a CONNECT BY clause.
MINUS		An operation that accepts two sets of rows and returns rows that appear in the first set but not in the second, eliminating duplicates.
NESTED LOOPS+		An operation that accepts two sets of rows, an outer set and an inner set. Oracle compares each row of the outer set with each row of the inner set and returns those rows that satisfy a condition.
	OUTER	A nested loops operation to perform an outer join statement.
PROJECTION		An internal operation.
REMOTE		A retrieval of data from a remote database.
SEQUENCE		An operation involving accessing values of a sequence.
SORT	AGGREGATE	A retrieval of a single row that is the result of applying a group function to a group of selected rows.
	UNIQUE	An operation that sorts a set of rows to eliminate duplicates.
	GROUP BY	An operation that sorts a set of rows into groups for a query with a GROUP BY clause.
	JOIN	An operation that sorts a set of rows before a merge-join operation.
	ORDER BY	An operation that sorts a set of rows for a query with an ORDER BY clause.
TABLE ACCESS*	FULL	A retrieval of all rows from a table.
	CLUSTER	A retrieval of rows from a table based on a value of the key of an indexed cluster.
	HASH	A retrieval of rows from a table based on a value of the key of hash cluster.
	BY ROWID	A retrieval of a row from a table based on its ROWID.
UNION		An operation that accepts two sets of rows and returns the union of the sets, eliminating duplicates.
VIEW		An operation that performs a view's query and then returns the resulting rows to another operation.

Note:

* These operations are access methods.
+ These operations are join operations.

Formatting EXPLAIN PLAN Output

• How to Run EXPLAIN PLAN

• Selecting PLAN_TABLE Output in Table Format

• Selecting PLAN_TABLE Output in Nested Format

How to Run EXPLAIN PLAN

SELECT ename, job, sal, dname
   FROM emp, dept
   WHERE emp.deptno = dept.deptno
      AND NOT EXISTS
         (SELECT *
            FROM salgrade
            WHERE emp.sal BETWEEN losal AND hisal);

This EXPLAIN PLAN statement generates an execution plan and places the output in PLAN_TABLE:

EXPLAIN PLAN
   SET STATEMENT_ID = 'Emp_Sal'
   FOR SELECT ename, job, sal, dname
      FROM emp, dept
      WHERE emp.deptno = dept.deptno
         AND NOT EXISTS
            (SELECT *
               FROM salgrade
               WHERE emp.sal BETWEEN losal AND hisal);

Selecting PLAN_TABLE Output in Table Format

SELECT operation, options, object_name, id, parent_id, position
    FROM plan_table
    WHERE statement_id = 'Emp_Sal'
   ORDER BY id;

OPERATION      OPTIONS OBJECT_NAME ID PARENT_ID POSITION COST CARDINALITY BYTES OTHER_TAG OPTIMIZER
--------------------------------------------------------------------------------------------------
SELECT STATEMENT                    0                  2    2           1    62           CHOOSE
FILTER                              1         0        1
NESTED LOOPS                        2         1        1    2           1    62
TABLE ACCESS    FULL    EMP         3         2        1    1           1    40           ANALYZED
TABLE ACCESS    FULL    DEPT        4         2        2                4    88           ANALYZED
TABLE ACCESS    FULL    SALGRADE    5         1        2    1           1    13           ANALYZED

The ORDER BY clause returns the steps of the execution plan sequentially by ID value. However, Oracle does not perform the steps in this order. PARENT_ID receives information from ID: yet more than one ID step fed into PARENT_ID. For example, step 2, a merge join, and step 7, a table access, both fed into step 1. A nested, visual representation of the processing sequence is shown in the next section.

The value of the POSITION column for the first row of output indicates the optimizer's estimated cost of executing the statement with this plan to be 5. For the other rows, it indicates the position relative to the other children of the same parent.

Selecting PLAN_TABLE Output in Nested Format

SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options
   ||' '||object_name
   ||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan"
   FROM plan_table
   START WITH id = 0 AND statement_id = 'Emp_Sal'
   CONNECT BY PRIOR id = parent_id AND statement_id ='Emp_Sal';
 
Query Plan
------------------------------
SELECT STATEMENT   Cost = 5
   FILTER
      NESTED LOOPS
         TABLE ACCESS FULL EMP
         TABLE ACCESS FULL DEPT
      TABLE ACCESS FULL SALGRADE  

The order resembles a tree structure, illustrated in the following figure.

Tree Structure of an Execution Plan

Prev Next

Copyright © 1996 Oracle Corporation. All Rights Reserved.

Library

Product

Contents

Index