Oracle7 Tuning, release 7.3.3 | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
This chapter shows how to use the SQL command EXPLAIN PLAN.
It covers the following topics:
See Also: For the syntax of the EXPLAIN PLAN command, see the Oracle7 Server SQL Reference.
The EXPLAIN PLAN command displays the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations that Oracle performs to execute the statement. By examining the execution plan, you can see exactly how Oracle executes your SQL statement.
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
Before you can issue an EXPLAIN PLAN statement, you must create a table to hold its output. Use one of the following approaches:
Any table used to store the output of the EXPLAIN PLAN command must have the same column names and datatypes as the PLAN_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);
The PLAN_TABLE used by the EXPLAIN PLAN command contains the following columns:
The following table describes the values which may appear in the OTHER_TAG column.
The following table lists each combination of OPERATION and OPTION values produced by the EXPLAIN PLAN command and its meaning within an execution plan.
* These operations are access methods.
+ These operations are join operations.
Both access methods and join operations are discussed in Appendix A, "Optimizer Concepts".
This section shows options for formatting EXPLAIN PLAN output
The following example shows a SQL statement and its corresponding execution plan generated by EXPLAIN PLAN. The sample query retrieves names and related information for employees whose salary is not within any range of the SALGRADE table:
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);
This SELECT statement generates the following output:
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.
This type of SELECT statement generates a nested representation of the output that more closely depicts the processing order used for the SQL statement.
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.
![]()
Figure 20-1: Tree Structure of an Execution Plan
The tree structure illustrates how operations that occur during the execution of a SQL statement feed one another. Each step in the execution plan is assigned a number (representing the ID column of the PLAN_TABLE) and is depicted by a "node". The result of each node's operation passes to its parent node, which uses it as input.
![]() ![]() Prev Next |
![]() Copyright © 1997 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |