Inspecting a SQL Statement's Execution Plan

Use the SQL Worksheet to inspect the execution plans chosen by the Oracle optimizer for SQL SELECT, UPDATE, INSERT, and DELETE statements. An execution plan is the sequence of operations that will be performed to execute the statement.

An execution plan shows a row source tree; the hierarchy of operations that comprise the statement. For each operation it shows the following information:

An ordering of the tables referenced by the statement
An access method for each table mentioned in the statement
A join method for tables affected by join operations in the statement
Data operations such as filter, sort, or aggregation

In addition to the row source tree, the plan table displays the following information about selected operations:

Optimization, such as the cost and cardinality of each operation
Partitioning, such as the set of accessed partitions
Parallel execution, such as the distribution method of join inputs

For more information, see the Using EXPLAIN PLAN section of the Oracle Database Performance Tuning Guide and Reference, available from the Oracle Technology Network (OTN) at http://www.oracle.com/pls/db92/db92.homepage.

To view a SQL statement's execution plan:

Enter a SQL statement in the worksheet's upper pane.
Do any one of the following:
- Press Shift-Alt-Enter.
- Click the 'Explain how the statement will be executed' button.
- Click the Right-click to open the context menu, and select Execute Explain Plan.
To view the execution plan, you need to have access to a table which stores the information for viewing. If the plan table does not yet exist, a dialog box will open to ask if it should be created. Click OK.
View the statement's result in the lower pane.