Using the Explain Plan Feature

Follow these steps to use the EXPLAIN PLAN functionality:

1. Open a Query window for a connection and then enter a SQL statement in the Query Window.
2. To customize the EXPLAIN PLAN output, set EXPLAIN PLAN Options. See Query Window Options Page.
3. In the Query Window, select a SQL statement or locate the cursor in a SQL statement, then select EXPLAIN PLAN from the Query Window Toolbar. The EXPLAIN PLAN result for the selected SQL statement appears in the EXPLAIN PLAN Result tab.

   EXPLAIN PLAN can only be applied to one SQL statement. If you select more than one SQL statement, only the EXPLAIN PLAN for the last SQL statement appears.

4. The EXPLAIN PLAN result appears in the EXPLAIN PLAN output window. You can cut and paste the results into a text file or email.

Description of the illustration querywindow001.gif

About EXPLAIN PLAN Result Tab

The result of the EXPLAIN PLAN for a SQL statement is a tree structure, with nodes that can be expanded, if applicable.

The Operation column is and always appears. Settings in the Query Window Options Page determine what other columns appear. You can see different columns by changing the EXPLAIN PLAN options.

When you locate the mouse in the Operation column, a tooltip appears displaying the full details of the Operation in that row. In the above example, when you locate the mouse in the row (SELECT statement), you can see the complete SQL statement for the Explain Plan result that you have selected in the Query Editor.

When you click on any column, the whole row is selected.

An error message appears here if there is any error in retrieving the EXPLAIN PLAN.

The columns in the result tab are described in the next section, EXPLAIN PLAN Columns Table.

EXPLAIN PLAN Columns Table

This table lists and describes the columns that can be viewed in the EXPLAIN PLAN result tab. Which columns appear depends on the settings of the EXPLAIN PLAN Options page, described in the next section, Query Window Options Page.

Column	Description
Operation	The operation in the execution plan. To view the complete SQL statement of the explain plan, locate the cursor in the top level row of a SQL statement section under the Operation column. The SQL text can then be viewed in a Tooltip control. This column always appears, regardless of settings.
Optimizer	Current mode of the optimizer. Default is checked.
Cost	Cost of the operation as estimated by the optimization approach. Cost is not determined for table access operations. The value of this column is a function of the CPU_COST and IO_COST columns, however, there is no particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. Default is checked.
Cardinality	Number of rows accessed by the operation, as estimated by the optimization approach. Default is checked.
Bytes	Number of bytes accessed by the operation, as estimated by the optimization approach. Default is checked.
Partition Start	Start partition of a range of accessed partitions. It can be one of the following values: n indicating that the SQL compiler has identified the start partition and given it partition number n. KEY indicating that the start partition is identified at run time from partitioning key values. ROW REMOVE_LOCATION indicating that the start partition (true for the stop partition also) is computed at run time from the location of each record being retrieved. The record location is obtained by a user or from a global index. INVALID indicating that the range of accessed partitions is empty. Default is unchecked.
Partition Stop	Step that has computed the pair of values of the PARTITION_START and PARTITION_STOP columns. Default is unchecked.
CPU Cost	CPU cost of the operation, as estimated by the optimization approach. The value of this column is proportional to the number of cycles required for the operation. For statements that use the rule-based approach, this column is null. Default is checked.
IO Cost	I/O cost of the operation, as estimated by the optimization approach. The value of this column is proportional to the number of data blocks read by the operation. For statements that use the rule-based approach, this column is null. Default is checked.
Access Predicates	Predicates used to locate rows in an access structure. For example, start or stop predicates for an index range scan. Default is unchecked.
Filter Predicates	Predicates used to filter rows before producing them. Default is unchecked.
Time	Elapsed time in seconds of the operation, as estimated by optimization approach. For statements that use the rule-based approach, this column is null. Default is checked.