Oracle9i Database Performance Guide and Reference Release 1 (9.0.1) Part Number A87503-02 |
|
This chapter introduces execution plans, describes the SQL statement EXPLAIN
PLAN
, and explains how to interpret its output. This chapter also provides procedures for managing outlines to control application performance characteristics.
This chapter contains the following sections:
The EXPLAIN
PLAN
statement displays execution plans chosen by the Oracle optimizer for SELECT
, UPDATE
, INSERT
, and DELETE
statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.
The row source tree is the core of the execution plan. It shows the following information:
In addition to the row source tree, the plan table contains information about the following:
The EXPLAIN
PLAN
results let you determine whether the optimizer selects a particular execution plan (for example, nested loops join). It also helps you to understand the optimizer decisions (for example, why the optimizer chose a nested loops join instead of a hash join), and explains the performance of a query.
Note: Oracle Performance Manager charts and Oracle SQL Analyze can automatically create and display explain plans for you. For more information on using explain plans, see the Database Tuning with the Oracle Tuning Pack manual. |
With the cost-based optimizer, execution plans can and do change as the underlying costs change. EXPLAIN
PLAN
output shows how Oracle runs the SQL statement when the statement was explained. This can differ from the plan during actual execution for a SQL statement, because of differences in the execution environment and explain plan environment.
Execution plans can differ due to the following:
Even if the schemas are the same, the optimizer can choose different execution plans if the costs are different. Some factors that affect the costs include the following:
The execution plan operation alone cannot differentiate between well-tuned statements and those that perform poorly. For example, an EXPLAIN
PLAN
output that shows that a statement uses an index does not necessarily mean that the statement runs efficiently. Sometimes indexes can be extremely inefficient. In this case, you should examine the following:
It is best to use EXPLAIN
PLAN
to determine an access plan, and then later prove that it is the optimal plan through testing. When evaluating a plan, examine the statement's actual resource consumption. Use Oracle Trace or the SQL trace facility and TKPROF
to examine individual SQL statement performance.
Before issuing an EXPLAIN
PLAN
statement, you must have a table to hold it's output.
Use 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 depends on your operating system. For example, it is located under $ORACLE_HOME/rdbms/admin
on Unix. PLAN_TABLE
is the default table into which the EXPLAIN
PLAN
statement inserts rows describing execution plans.
Oracle Corporation recommends that you drop and rebuild the PLAN_TABLE
table after upgrading the version of the database. This is because the columns might change, which can cause scripts (or TKPROF
, if you are specifying the table) to fail.
If you want an output table with a different name, then create PLAN_TABLE
and rename it with the RENAME
SQL statement.
To explain a SQL statement, enter the following:
EXPLAIN PLAN FOR <<SQL Statement>>
This explains the plan into the PLAN_TABLE
table. You can then select the execution plan from PLAN_TABLE
. This is useful if you do not have any other plans in PLAN_TABLE
, or if you only want to look at the last statement.
For example:
EXPLAIN PLAN FOR SELECT name FROM emp;
With multiple statements, you can specify a statement identifier and use that to identify your specific execution plan. Before using a statement ID, remove any existing rows for that statement ID.
For example:
EXPLAIN PLAN SET STATEMENT_ID = <<identifier>> FOR <<SQL Statement>>
Specifically:
EXPLAIN PLAN SET STATEMENT_ID = 'bad1' FOR SELECT name FROM emp;
You can specify the INTO
clause to specify a different table.
For example:
EXPLAIN PLAN INTO <<different table>> FOR <<SQL Statement>>
Specifically:
EXPLAIN PLAN INTO my_plan_table FOR SELECT name FROM emp;
Or:
EXPLAIN PLAN INTO my_plan_table SET STATEMENT_ID = 'bad1' FOR SELECT name FROM emp;
After you have explained the plan, use the two scripts provided by Oracle to display the most recent plan table output:
UTLXPLS
.SQL
- Shows plan table output for serial processing
UTLXPLP
.SQL
- Shows plan table output with parallel execution columns
If you have specified a statement identifier, then you can write your own script to query the PLAN_TABLE
.
For example:
STATEMENT_ID
.
CONNECT
BY
clause to walk the tree from parent to child, the join keys being STATEMENT_ID
= PRIOR
STATEMENT_ID
and PARENT_ID
= PRIOR
ID
.
LEVEL
(associated with CONNECT
BY
) to indent the children.
SELECT cardinality "Rows", lpad(' ',level-1)||operation||' '|| options||' '||object_name "Plan" FROM PLAN_TABLE CONNECT BY prior id = parent_id AND prior statement_id = statement_id START WITH id = 0 AND statement_id = 'bad1' ORDER BY id; Rows Plan ------- ---------------------------------------- SELECT STATEMENT TABLE ACCESS FULL EMP
The NULL
in the Rows
column indicates that the optimizer does not have any statistics on the table. Analyzing the table shows the following:
Rows Plan ------- ---------------------------------------- 16957 SELECT STATEMENT 16957 TABLE ACCESS FULL EMP
You can also select the COST
. This is useful for comparing execution plans or for understanding why the optimizer chooses one execution plan over another.
This section uses progressively complex examples to illustrate execution plans.
The following statement is used to display the execution plan:
SELECT lpad(' ',level-1)||operation||' '||options||' '|| object_name "Plan" FROM plan_table CONNECT BY prior id = parent_id AND prior statement_id = statement_id START WITH id = 0 AND statement_id = '&1' ORDER BY id;
EXPLAIN PLAN SET statement_id = 'example1' FOR SELECT full_name FROM per_all_people_f WHERE UPPER(full_name) LIKE 'Pe%' ; Plan --------------------------------------------- SELECT STATEMENT TABLE ACCESS FULL PER_ALL_PEOPLE_F
This plan shows execution of a SELECT
statement. The table PER_ALL_PEOPLE_F
is accessed using a full table scan.
PER_ALL_PEOPLE_F
is accessed, and the WHERE
clause criteria is evaluated for every row.
SELECT
statement returns the rows meeting the WHERE
clause criteria.
EXPLAIN PLAN SET statement_id = 'example2' FOR SELECT full_name FROM per_all_people_f WHERE full_name LIKE 'Pe%' ; Plan --------------------------------------------- SELECT STATEMENT TABLE ACCESS BY INDEX ROWID PER_ALL_PEOPLE_F INDEX RANGE SCAN PER_PEOPLE_F_N54
This plan shows execution of a SELECT
statement.
PER_PEOPLE_F_N54
is used in a range scan operation.
PER_ALL_PEOPLE_F
is accessed via ROWID
. ROWID
s are obtained from the index in the previous step for keys that meet the WHERE
clause criteria. When the table is accessed, any additional WHERE
clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated.
SELECT
statement returns rows satisfying the WHERE
clause conditions (evaluated in previous steps).
EXPLAIN PLAN SET statement_id = 'example3' FOR SELECT segment1, segment2, description, inventory_item_id FROM mtl_system_items msi WHERE segment1 = :b1 AND segment2 LIKE '%-BOM' AND NVL(end_date_active,sysdate+1) > SYSDATE ; Plan -------------------------------------------------- SELECT STATEMENT TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS INDEX RANGE SCAN MTL_SYSTEM_ITEMS_N8
This plan shows execution of a SELECT
statement.
MTL_SYSTEM_ITEMS_N8
is used in a range scan operation. This is an index on (SEGMENT1
, SEGMENT2
, SEGMENT3
). The range scan happens using the following condition:
segment1
=
:b1
The rows that come out of this step satisfy all the WHERE
clause criteria that can be evaluated with the index columns. Therefore, the following condition is also evaluated at this stage:
segment2 LIKE '%-BOM'
PER_ALL_PEOPLE_F
is accessed via ROWID
s obtained from the index in the previous step. When the table is accessed, any additional WHERE
clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. Therefore, the following condition is evaluated at this stage:
NVL(end_date_active,sysdate+1) > SYSDATE
SELECT
statement returns rows satisfying the WHERE
clause conditions (evaluated in previous steps).
EXPLAIN PLAN SET statement_id = 'example4' FOR SELECT h.order_number, l.revenue_amount, l.ordered_quantity FROM so_headers_all h, so_lines_all l WHERE h.customer_id = :b1 AND h.date_ordered > SYSDATE-30 AND l.header_id = h.header_id ; Plan -------------------------------------------------- SELECT STATEMENT NESTED LOOPS TABLE ACCESS BY INDEX ROWID SO_HEADERS_ALL INDEX RANGE SCAN SO_HEADERS_N1 TABLE ACCESS BY INDEX ROWID SO_LINES_ALL INDEX RANGE SCAN SO_LINES_N1
This plan shows execution of a SELECT
statement.
so_headers_n1
is used in a range scan operation. This is an index on customer_id
. The range scan happens using the following condition:
customer_id = :b1
so_headers_all
is accessed via ROWID
s obtained from the index in the previous step. When the table is accessed, any additional WHERE
clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. Therefore, the following condition is evaluated at this stage:
h.date_ordered > sysdate-30
so_headers_all
satisfying the WHERE
clause conditions, a range scan is run on so_lines_n1
using the following condition:
l.header_id = h.header_id
so_lines_all
is accessed via ROWID
s obtained from the index in the previous step. When the table is accessed, any additional WHERE
clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. There are no additional conditions to evaluate here.
SELECT
statement returns rows satisfying the WHERE
clause conditions (evaluated in previous steps).
Index row sources using bitmap indexes appear in the EXPLAIN
PLAN
output with the word BITMAP
indicating the type of the index. Consider the following sample query and plan:
EXPLAIN PLAN FORSELECT * FROM t WHERE c1 = 2 AND c2 <> 6 OR c3 BETWEEN 10 AND 20;SELECT STATEMENT TABLE ACCESS T BY INDEX ROWID BITMAP CONVERSION TO ROWID BITMAP OR BITMAP MINUS BITMAP MINUS BITMAP INDEX C1_IND SINGLE VALUE BITMAP INDEX C2_IND SINGLE VALUE BITMAP INDEX C2_IND SINGLE VALUE BITMAP MERGE BITMAP INDEX C3_IND RANGE SCAN
In this example, the predicate c1
=2
yields a bitmap from which a subtraction can take place. From this bitmap, the bits in the bitmap for c2
= 6
are subtracted. Also, the bits in the bitmap for c2
IS
NULL
are subtracted, explaining why there are two MINUS
row sources in the plan. The NULL
subtraction is necessary for semantic correctness unless the column has a NOT
NULL
constraint. The TO
ROWIDS
option is used to generate the ROWIDs
that are necessary for the table access.
Use EXPLAIN
PLAN
to see how Oracle accesses partitioned objects for specific queries.
Partitions accessed after pruning are shown in the PARTITION
START
and PARTITION
STOP
columns. The row source name for the range partition is "PARTITION
RANGE
". For hash partitions, the row source name is PARTITION
HASH
.
A join is implemented using partial partition-wise join if the DISTRIBUTION
column of the plan table of one of the joined tables contains PARTITION
(KEY
). Partial partition-wise join is possible if one of the joined tables is partitioned on its join column and the table is parallelized.
A join is implemented using full partition-wise join if the partition row source appears before the join row source in the EXPLAIN
PLAN
output. Full partition-wise joins are possible only if both joined tables are equi-partitioned on their respective join columns. Examples of execution plans for several types of partitioning follow.
Consider the following table, emp_range
, partitioned by range on hiredate
to illustrate how pruning is displayed. Assume that the tables emp
and dept
from a standard Oracle schema exist.
CREATE TABLE emp_range PARTITION BY RANGE(hiredate) (PARTITION emp_p1 VALUES LESS THAN (TO_DATE('1-JAN-1991','DD-MON-YYYY')), PARTITION emp_p2 VALUES LESS THAN (TO_DATE('1-JAN-1993','DD-MON-YYYY')), PARTITION emp_p3 VALUES LESS THAN (TO_DATE('1-JAN-1995','DD-MON-YYYY')), PARTITION emp_p4 VALUES LESS THAN (TO_DATE('1-JAN-1997','DD-MON-YYYY')), PARTITION emp_p5 VALUES LESS THAN (TO_DATE('1-JAN-1999','DD-MON-YYYY'))) AS SELECT * FROM emp;
For the first example, consider the following statement:
EXPLAIN PLAN FOR SELECT * FROM emp_range;
Enter the following to display the EXPLAIN
PLAN
output:
@?/RDBMS/ADMIN/UTLXPLS
Oracle displays something similar to the following:
Plan Table ------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart | Pstop| ------------------------------------------------------------------------------- | SELECT STATEMENT | | 105 | 8K| 1 | | | | PARTITION RANGE ALL | | | | | 1 | 5 | | TABLE ACCESS FULL |EMP_RANGE | 105 | 8K| 1 | 1 | 5 | ------------------------------------------------------------------------------- 6 rows selected.
A partition row source is created on top of the table access row source. It iterates over the set of partitions to be accessed.
In the above example, the partition iterator covers all partitions (option ALL
), because a predicate was not used for pruning. The PARTITION_START
and PARTITION
_STOP
columns of the PLAN_TABLE
show access to all partitions from 1 to 5.
For the next example, consider the following statement:
EXPLAIN PLAN FOR SELECT * FROM emp_range WHERE hiredate >= TO_DATE('1-JAN-1995','DD-MON-YYYY'); Plan Table -------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | -------------------------------------------------------------------------------- | SELECT STATEMENT | | 3 | 54 | 1 | | | | PARTITION RANGE ITERATOR | | | | | 4 | 5 | | TABLE ACCESS FULL |EMP_RANGE | 3 | 54 | 1 | 4 | 5 | -------------------------------------------------------------------------------- 6 rows selected.
In the above example, the partition row source iterates from partition 4 to 5, because we prune the other partitions using a predicate on hiredate
.
Finally, consider the following statement:
EXPLAIN PLAN FOR SELECT * FROM emp_range WHERE hiredate < TO_DATE('1-JAN-1991','DD-MON-YYYY'); Plan Table -------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | -------------------------------------------------------------------------------- | SELECT STATEMENT | | 2 | 36 | 1 | | | | TABLE ACCESS FULL |EMP_RANGE | 2 | 36 | 1 | 1 | 1 | -------------------------------------------------------------------------------- 5 rows selected.
In the above example, only partition 1 is accessed and known at compile time; thus, there is no need for a partition row source.
Oracle displays the same information for hash partitioned objects, except the partition row source name is PARTITION
HASH
instead of PARTITION
RANGE
. Also, with hash partitioning, pruning is only possible using equality or IN
-list predicates.
To illustrate how Oracle displays pruning information for composite partitioned objects, consider the table emp_comp
that is range partitioned on hiredate
and subpartitioned by hash on deptno
.
CREATE TABLE emp_comp PARTITION BY RANGE(hiredate) SUBPARTITION BY HASH(deptno) SUBPARTITIONS 3 (PARTITION emp_p1 VALUES LESS THAN (TO_DATE('1-JAN-1991','DD-MON-YYYY')), PARTITION emp_p2 VALUES LESS THAN (TO_DATE('1-JAN-1993','DD-MON-YYYY')), PARTITION emp_p3 VALUES LESS THAN (TO_DATE('1-JAN-1995','DD-MON-YYYY')), PARTITION emp_p4 VALUES LESS THAN (TO_DATE('1-JAN-1997','DD-MON-YYYY')), PARTITION emp_p5 VALUES LESS THAN (TO_DATE('1-JAN-1999','DD-MON-YYYY'))) AS SELECT * FROM emp;
For the first example, consider the following statement:
EXPLAIN PLAN FOR SELECT * FROM emp_comp; Plan Table -------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart | Pstop | -------------------------------------------------------------------------------- | SELECT STATEMENT | | 105 | 8K| 1 | | | | PARTITION RANGE ALL | | | | | 1 | 5 | | PARTITION HASH ALL | | | | | 1 | 3 | | TABLE ACCESS FULL |EMP_COMP | 105 | 8K| 1 | 1 | 15| -------------------------------------------------------------------------------- 7 rows selected.
This example shows the plan when Oracle accesses all subpartitions of all partitions of a composite object. Two partition row sources are used for that purpose: a range partition row source to iterate over the partitions and a hash partition row source to iterate over the subpartitions of each accessed partition.
In the following example, the range partition row source iterates from partition 1 to 5, because no pruning is performed. Within each partition, the hash partition row source iterates over subpartitions 1 to 3 of the current partition. As a result, the table access row source accesses subpartitions 1 to 15. In other words, it accesses all subpartitions of the composite object.
EXPLAIN PLAN FOR SELECT * FROM emp_comp
WHERE hiredate = TO_DATE('15-FEB-1997', 'DD-MON-YYYY');
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 1 | 96 | 1 | | |
| PARTITION HASH ALL | | | | | 1 | 3 |
| TABLE ACCESS FULL |EMP_COMP
| 1 | 96 | 1 | 13 | 15 |
--------------------------------------------------------------------------------
6 rows selected.
In the above example, only the last partition, partition 5, is accessed. This partition is known at compile time, so we do not need to show it in the plan. The hash partition row source shows accessing of all subpartitions within that partition; that is, subpartitions 1 to 3, which translates into subpartitions 13 to 15 of the emp_comp
table.
Now consider the following statement:
EXPLAIN PLAN FOR SELECT * FROM emp_comp WHERE deptno = 20; Plan Table -------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | -------------------------------------------------------------------------------- | SELECT STATEMENT | | 2 | 200 | 1 | | | | PARTITION RANGE ALL | | | | | 1 | 5 | | TABLE ACCESS FULL |EMP_COMP | 2 | 200 | 1 | | | -------------------------------------------------------------------------------- 6 rows selected.
In the above example, the predicate deptno
= 20 enables pruning on the hash dimension within each partition, so Oracle only needs to access a single subpartition. The number of that subpartition is known at compile time, so the hash partition row source is not needed.
Finally, consider the following statement:
VARIABLE dno NUMBER; EXPLAIN PLAN FOR SELECT * FROM emp_comp WHERE deptno = :dno; Plan Table -------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | -------------------------------------------------------------------------------- | SELECT STATEMENT | | 2 | 200 | 1 | | | | PARTITION RANGE ALL | | | | | 1 | 5 | | PARTITION HASH SINGLE | | | | | KEY | KEY | | TABLE ACCESS FULL |EMP_COMP | 2 | 200 | 1 | | | -------------------------------------------------------------------------------- 7 rows selected.
The last two examples are the same, except that deptno
= 20 has been replaced by deptno
= :dno
. In this last case, the subpartition number is unknown at compile time, and a hash partition row source is allocated. The option is SINGLE
for that row source, because Oracle accesses only one subpartition within each partition. The PARTITION
_START
and PARTITION
_STOP
is set to KEY
. This means that Oracle determines the number of the subpartition at run time.
In the following example, emp_range
is joined on the partitioning column and is parallelized. This enables use of partial partition-wise join, because the dept
table is not partitioned. Oracle dynamically partitions the dept
table before the join.
ALTER TABLE emp PARALLEL 2;Table altered.ALTER TABLE dept PARALLEL 2;Table altered.
To show the plan for the query, enter:
EXPLAIN PLAN FOR SELECT /*+ ORDERED USE_HASH(D) */ ename, dname FROM emp_range e, dept d WHERE e.deptno = d.deptnoAND e.hiredate > TO_DATE('29-JUN-1996','DD-MON-YYYY');
Plan Table ------------------------------------------------------------------------------------------------------------ | Operation | Name | Rows | Bytes| Cost | TQ |IN-OUT| PQ Distrib | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------ | SELECT STATEMENT | | 1 | 51 | 3 | | | | | | | HASH JOIN | | 1 | 51 | 3 | 2,02 | P->S |QC (RANDOM) | | | | PARTITION RANGE ITERATOR | | | | | 2,02 | PCWP | | 4 | 5 | | TABLE ACCESS FULL |EMP_RANGE | 3 | 87 | 1 | 2,00 | PCWP | | 4 | 5 | | TABLE ACCESS FULL |DEPT | 21 | 462 | 1 | 2,01 | P->P |PART (KEY) | | | ------------------------------------------------------------------------------------------------------------ 8 rows selected.
The plan shows that the optimizer selects partition-wise join, because the PQ
Distrib
column contains the text PART
(KEY
), or partition key.
In the next example, emp_comp
is joined on its hash partitioning column, deptno
, and is parallelized. This enables use of partial partition-wise join, because the dept
table is not partitioned. Again, Oracle dynamically partitions the dept
table.
ALTER TABLE emp_comp PARALLEL 2;Table altered.EXPLAIN PLAN FOR SELECT /*+ ORDERED USE_HASH(D) */ ename, dnameFROM emp_comp e, dept d WHERE e.deptno = d.deptno AND e.hiredate > TO_DATE('13-MAR-1995','DD-MON-YYYY');
Plan Table ------------------------------------------------------------------------------------------------------------ | Operation | Name | Rows | Bytes| Cost | TQ |IN-OUT| PQ Distrib | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------ | SELECT STATEMENT | | 1 | 51 | 3 | | | | | | | HASH JOIN | | 1 | 51 | 3 | 0,01 | P->S | QC (RANDOM)| | | | PARTITION RANGE ITERATOR | | | | | 0,01 | PCWP | | 4 | 5 | | PARTITION HASH ALL | | | | | 0,01 | PCWP | | 1 | 3 | | TABLE ACCESS FULL |EMP_COMP | 3 | 87 | 1 | 0,01 | PCWP | | 10 | 15 | | TABLE ACCESS FULL |DEPT | 21 | 462 | 1 | 0,00 | P->P | PART (KEY) | | | ------------------------------------------------------------------------------------------------------------ 9 rows selected.
In the following example, emp_comp
and dept_hash
are joined on their hash partitioning columns. This enables use of full partition-wise join. The PARTITION
HASH
row source appears on top of the join row source in the plan table output.
To create the table dept_hash
, enter:
CREATE TABLE dept_hashPARTITION BY HASH(deptno) PARTITIONS 3 PARALLEL AS SELECT * FROM dept;
To show the plan for the query, enter:
EXPLAIN PLAN FOR SELECT /*+ ORDERED USE_HASH(D) */ ename, dnameFROM emp_comp e, dept_hash d WHERE e.deptno = d.deptnoAND e.hiredate > TO_DATE('29-JUN-1996','DD-MON-YYYY');
Plan Table ------------------------------------------------------------------------------------------------------------ | Operation | Name | Rows | Bytes| Cost | TQ |IN-OUT| PQ Distrib | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------ | SELECT STATEMENT | | 2 | 102| 2 | | | | | | | PARTITION HASH ALL | | | | | 4,00| PCWP | | 1 | 3 | | HASH JOIN | | 2 | 102 | 2 | 4,00| P->S | QC (RANDOM)| | | | PARTITION RANGE ITERATOR | | | | | 4,00| PCWP | | 4 | 5 | | TABLE ACCESS FULL |EMP_COMP | 3 | 87 | 1 | 4,00| PCWP | | 10 | 15 | | TABLE ACCESS FULL |DEPT_HASH | 63 | 1K| 1 | 4,00| PCWP | | 1 | 3 | ------------------------------------------------------------------------------------------------------------ 9 rows selected.
An INLIST
ITERATOR
operation appears in the EXPLAIN
PLAN
output if an index implements an IN
-list predicate. For example:
SELECT * FROM emp WHERE empno IN (7876, 7900, 7902);
The EXPLAIN
PLAN
output appears as follows:
OPERATION OPTIONS OBJECT_NAME ---------------- --------------- -------------- SELECT STATEMENT INLIST ITERATOR TABLE ACCESS BY ROWID EMP INDEX RANGE SCAN EMP_EMPNO
The INLIST
ITERATOR
operation iterates over the operation below it for each value in the IN
-list predicate. For partitioned tables and indexes, the three possible types of IN
-list columns are described in the following sections.
If the IN
-list column empno
is an index column but not a partition column, then the plan is as follows (the IN
-list operator appears above the table operation but below the partition operation):
OPERATION OPTIONS OBJECT_NAME PARTITION_START PARTITION_STOP ---------------- ------------ ----------- --------------- -------------- SELECT STATEMENT PARTITION RANGE ALL KEY(INLIST) KEY(INLIST) INLIST ITERATOR TABLE ACCESS BY LOCAL INDEX ROWID EMP KEY(INLIST) KEY(INLIST) INDEX RANGE SCAN EMP_EMPNO KEY(INLIST) KEY(INLIST)
The KEY
(INLIST
) designation for the partition start and stop keys specifies that an IN
-list predicate appears on the index start/stop keys.
If empno
is an indexed and a partition column, then the plan contains an INLIST
ITERATOR
operation above the partition operation:
OPERATION OPTIONS OBJECT_NAME PARTITION_START PARTITION_STOP ---------------- ------------ ----------- --------------- -------------- SELECT STATEMENT INLIST ITERATOR PARTITION RANGE ITERATOR KEY(INLIST) KEY(INLIST) TABLE ACCESS BY LOCAL INDEX ROWID EMP KEY(INLIST) KEY(INLIST) INDEX RANGE SCAN EMP_EMPNO KEY(INLIST) KEY(INLIST)
If empno
is a partition column and there are no indexes, then no INLIST
ITERATOR
operation is allocated:
OPERATION OPTIONS OBJECT_NAME PARTITION_START PARTITION_STOP ---------------- ------------ ----------- --------------- -------------- SELECT STATEMENT PARTITION RANGE INLIST KEY(INLIST) KEY(INLIST) TABLE ACCESS FULL EMP KEY(INLIST) KEY(INLIST)
If emp_empno
is a bitmap index, then the plan is as follows:
OPERATION OPTIONS OBJECT_NAME ---------------- --------------- -------------- SELECT STATEMENT INLIST ITERATOR TABLE ACCESS BY INDEX ROWID EMP BITMAP CONVERSION TO ROWIDS BITMAP INDEX SINGLE VALUE EMP_EMPNO
You can also use EXPLAIN
PLAN
to derive user-defined CPU and I/O costs for domain indexes. EXPLAIN
PLAN
displays these statistics in the OTHER
column of PLAN_TABLE
.
For example, assume table emp
has user-defined operator CONTAINS
with a domain index emp_resume
on the resume
column, and the index type of emp_resume
supports the operator CONTAINS
. Then the query:
SELECT * FROM emp WHERE CONTAINS(resume, 'Oracle') = 1
might display the following plan:
OPERATION OPTIONS OBJECT_NAME OTHER ----------------- ----------- ------------ ---------------- SELECT STATEMENT TABLE ACCESS BY ROWID EMP DOMAIN INDEX EMP_RESUME CPU: 300, I/O: 4
Oracle does not support EXPLAIN
PLAN
for statements performing implicit type conversion of date bind variables. With bind variables in general, the EXPLAIN
PLAN
output might not represent the real execution plan.
From the text of a SQL statement, TKPROF
cannot determine the types of the bind variables. It assumes that the type is CHARACTER
, and gives an error message if this is not the case. You can avoid this limitation by putting appropriate type conversions in the SQL statement.
The PLAN_TABLE
used by the EXPLAIN
PLAN
statement contains the following columns:
Column | Type | Description |
STATEMENT_ID |
VARCHAR2(30) |
The value of the optional |
TIMESTAMP |
DATE |
The date and time when the |
REMARKS |
VARCHAR2(80) |
Any comment (of up to 80 bytes) you want to associate with each step of the explained plan. If you need to add or change a remark on any row of the |
OPERATION |
VARCHAR2(30) |
The name of the internal operation performed in this step. In the first row generated for a statement, the column contains one of the following values: DELETE STATEMENT INSERT STATEMENT SELECT STATEMENT UPDATE STATEMENT See Table 9-4 for more information on values for this column. |
OPTIONS |
VARCHAR2(225) |
A variation on the operation described in the See Table 9-4 for more information on values for this column. |
OBJECT_NODE |
VARCHAR2(128) |
The name of the database link used to reference the object (a table name or view name). For local queries using parallel execution, this column describes the order in which output from operations is consumed. |
OBJECT_OWNER |
VARCHAR2(30) |
The name of the user who owns the schema containing the table or index. |
OBJECT_NAME |
VARCHAR2(30) |
The name of the table or index. |
OBJECT_INSTANCE |
NUMERIC |
A number corresponding to the ordinal position of the object as it appears in the original statement. The numbering proceeds from left to right, outer to inner with respect to the original statement text. View expansion results in unpredictable numbers. |
OBJECT_TYPE |
VARCHAR2(30) |
A modifier that provides descriptive information about the object; for example, |
OPTIMIZER |
VARCHAR2(255) |
The current mode of the optimizer. |
SEARCH_COLUMNS |
NUMBERIC |
Not currently used. |
ID |
NUMERIC |
A number assigned to each step in the execution plan. |
PARENT_ID |
NUMERIC |
The ID of the next execution step that operates on the output of the |
POSITION |
NUMERIC |
For the first row of output, this indicates the optimizer's estimated cost of executing the statement. For the other rows, it indicates the position relative to the other children of the same parent. |
COST |
NUMERIC |
The cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the |
CARDINALITY |
NUMERIC |
The estimate by the cost-based approach of the number of rows accessed by the operation. |
BYTES |
NUMERIC |
The estimate by the cost-based approach of the number of bytes accessed by the operation. |
OTHER_TAG |
VARCHAR2(255) |
Describes the contents of the |
PARTITION_START |
VARCHAR2(255) |
The start partition of a range of accessed partitions. It can take one of the following values: n indicates that the start partition has been identified by the SQL compiler, and its partition number is given by n.
|
PARTITION_STOP |
VARCHAR2(255) |
The stop partition of a range of accessed partitions. It can take one of the following values: n indicates that the stop partition has been identified by the SQL compiler, and its partition number is given by n.
|
PARTITION_ID |
NUMERIC |
The step that has computed the pair of values of the |
OTHER |
LONG |
Other information that is specific to the execution step that a user might find useful. |
DISTRIBUTION |
VARCHAR2(30) |
Stores the method used to distribute rows from producer query servers to consumer query servers. See Table 9-3 for more information on the possible values for this column. For more information about consumer and producer query servers, see Oracle9i Database Concepts. |
CPU_COST |
NUMERIC |
The CPU cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null. The value of this column is proportional to the number of machine cycles required for the operation. |
IO_COST |
NUMERIC |
The I/O cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null. The value of this column is proportional to the number of data blocks read by the operation. |
TEMP_SPACE |
NUMERIC |
The temporary space, in bytes, used by the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, or for operations that don't use any temporary space, this column is null. |
Table 9-2 describes the values that can appear in the OTHER_TAG
column.
Table 9-3 describes the values that can appear in the DISTRIBUTION
column:
Table 9-4 lists each combination of OPERATION
and OPTION
produced by the EXPLAIN
PLAN
statement and its meaning within an execution plan.
Operation | Option | Description |
---|---|---|
AND-EQUAL |
|
Operation accepting multiple sets of rowids, returning the intersection of the sets, eliminating duplicates. Used for the single-column indexes access path. |
|
CONVERSION |
|
|
INDEX |
|
|
MERGE |
Merges several bitmaps resulting from a range scan into one bitmap. |
|
MINUS |
Subtracts bits of one bitmap from another. Row source is used for negated predicates. Can be used only if there are nonnegated predicates yielding a bitmap from which the subtraction can take place. An example appears in "Viewing Bitmap Indexes with EXPLAIN PLAN". |
|
OR |
Computes the bitwise |
CONNECT BY |
|
Retrieves rows in hierarchical order for a query containing a |
CONCATENATION |
|
Operation accepting multiple sets of rows returning the union-all of the sets. |
COUNT |
|
Operation counting the number of rows selected from a table. |
STOPKEY |
Count operation where the number of rows returned is limited by the |
|
DOMAIN INDEX |
|
Retrieval of one or more rowids from a domain index. The options column contain information supplied by a user-defined domain index cost function, if any. |
FILTER |
|
Operation accepting a set of rows, eliminates some of them, and returns the rest. |
FIRST ROW |
|
Retrieval of only the first row selected by a query. |
FOR UPDATE |
|
Operation retrieving and locking the rows selected by a query containing a |
HASH JOIN (These are join operations.) |
|
Operation joining two sets of rows and returning the result. |
ANTI |
Hash anti-join. |
|
SEMI |
Hash semi-join. |
|
INDEX (These are access methods.) |
UNIQUE SCAN |
Retrieval of a single rowid from an index. |
RANGE SCAN |
Retrieval of one or more rowids from an index. Indexed values are scanned in ascending order. |
|
RANGE SCAN DESCENDING |
Retrieval of one or more rowids from an index. Indexed values are scanned in descending order. |
|
INLIST ITERATOR |
|
Iterates over the operation below it for each value in the |
INTERSECTION |
|
Operation accepting two sets of rows and returning the intersection of the sets, eliminating duplicates. |
MERGE JOIN (These are join operations.) |
|
Operation accepting two sets of rows, each sorted by a specific value, combining each row from one set with the matching rows from the other, and returning the result. |
OUTER |
Merge join operation to perform an outer join statement. |
|
ANTI |
Merge anti-join. |
|
SEMI |
Merge semi-join. |
|
CONNECT BY |
|
Retrieval of rows in hierarchical order for a query containing a |
MINUS |
|
Operation accepting two sets of rows and returning rows appearing in the first set but not in the second, eliminating duplicates. |
NESTED LOOPS (These are join operations.) |
|
Operation accepting 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, returning rows that satisfy a condition. |
OUTER |
Nested loops operation to perform an outer join statement. |
|
PARTITION |
SINGLE |
Access one partition. |
|
ITERATOR |
Access many partitions (a subset). |
|
ALL |
Access all partitions. |
|
INLIST |
Similar to iterator, but based on an |
|
INVALID |
Indicates that the partition set to be accessed is empty. |
|
|
Iterates over the operation below it for each partition in the range given by the
|
REMOTE |
|
Retrieval of data from a remote database. |
SEQUENCE |
|
Operation involving accessing values of a sequence. |
SORT |
AGGREGATE |
Retrieval of a single row that is the result of applying a group function to a group of selected rows. |
UNIQUE |
Operation sorting a set of rows to eliminate duplicates. |
|
GROUP BY |
Operation sorting a set of rows into groups for a query with a |
|
JOIN |
Operation sorting a set of rows before a merge-join. |
|
ORDER BY |
Operation sorting a set of rows for a query with an |
|
TABLE ACCESS (These are access methods.) |
FULL |
Retrieval of all rows from a table. |
SAMPLE |
Retrieval of sampled rows from a table. |
|
CLUSTER |
Retrieval of rows from a table based on a value of an indexed cluster key. |
|
HASH |
Retrieval of rows from table based on hash cluster key value. |
|
BY ROWID RANGE |
Retrieval of rows from a table based on a rowid range. |
|
|
SAMPLE BY ROWID RANGE |
Retrieval of sampled rows from a table based on a rowid range. |
|
BY USER ROWID |
If the table rows are located using user-supplied rowids. |
|
BY INDEX ROWID |
If the table is nonpartitioned and rows are located using index(es). |
|
BY GLOBAL INDEX ROWID |
If the table is partitioned and rows are located using only global indexes. |
|
BY LOCAL INDEX ROWID |
If the table is partitioned and rows are located using one or more local indexes and possibly some global indexes. |
|
|
The partition boundaries might have been computed by:
A previous
The |
UNION |
|
Operation accepting two sets of rows and returns the union of the sets, eliminating duplicates. |
VIEW |
|
Operation performing a view's query and then returning the resulting rows to another operation. |
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|