SQL Plan Page
Oracle generates the execution plan for a specified SQL statement by using the Oracle query optimizer to determine which of the possible execution methods is the most efficient way to retrieve the required data.
The SQL Plan page shows details of the SQL execution plan for the SQL statement selected in the Top SQL table.
To open the SQL Plan page
-
Select the Spotlight on Oracle connection in the Spotlight Browser.
-
Click SQL & Application Workload | Top SQL Page.
- Select a SQL Statement in the Top SQL grid.
- Click SQL Plan.
Note: Click Full screen for an expanded view of SQL Details. SQL Details (Full Screen) Page
SQL Plan (Grid)
SQL Plan shows the details of the SQL execution plan chosen by the Oracle query optimizer for the specified SQL statement.
Notes:
-
The SQL plan table shows the execution plan actually used when executing the SQL statement.
You may want to compare this cached execution plan to that generated by Spotlight's Explain Plan utility for the same SQL statement. The version of the execution plan shown within Explain Plan is generated in isolation, and may not reflect the SQL statement's execution environment. Tools | Explain Plan
- Not all columns are visible by default. Use the Column Organizer to view hidden columns. Show, Hide & Order Columns
| Column | Description |
|---|---|
|
Operation |
The operation performed in each step of the plan. |
|
ID |
The value assigned to each step of the execution plan. |
|
Cost |
The cost of the operation as determined by the query optimizer. |
|
Cardinality |
The optimizer's estimate of the number of rows accessed by the operation. |
|
Executions |
The number of times the operation is executed. |
|
Elapsed Time (ms) |
The elapsed time spent performing the operation. |
|
Buffer Gets |
The number of logical reads performed. |
|
Disk Reads |
The number of physical disk reads performed. |
|
Rows |
The number of rows retrieved. |
|
Predicates |
The predicates used to locate rows in an access structure. |
|
CPU Cost |
The optimizer's estimate of the CPU cost of the operation. |
|
Filter |
The filter applied to accept or reject each row retrieved. |
|
IO Cost |
The optimizer's estimate of the I/O cost of the operation. |
|
Other |
Other information about the operation that may be useful. |
|
OtherTag |
A value that describes what is contained in the Other column. |
|
ParentId |
The ID of the parent of the current step, which is processed after the current step. (The output of the current step is fed into its parent step.) |
|
Partition End |
The ending partition for the partitions accessed during the operation. |
|
Partition Start |
The starting partition for the partitions accessed during the operation. |
|
PartitionId |
The ID for the step that calculated the values of Partition End and Partition Start. |
|
Projection |
The expressions produced by the operation. |
|
Qblock |
The name of the query block for the operation. |
|
Temp Space |
The optimizer's estimate of the temporary space used by the operation. |
Plan Step Statistics (Grid)
Each step in the SQL execution plan returns a row set that is either used by the next step in the plan, OR returned to the user or application that submitted the SQL statement for processing.
|
Column |
Description |
|---|---|
|
Source |
The control structure within the step that produces the row set. |
|
Count |
The number of times the step was performed. |
|
Cost |
The optimizer's estimate of the cost of the step. |
|
Time (ms) |
The time taken to return a row set. |
|
Rows |
The number of rows retrieved in the step. |
Note: If the table contains no data, the Oracle STATISTICS_LEVEL parameter may be set too low. To collect plan step statistics for the SQL execution plan, open the Configuration and Memory | Oracle Parameters Page and set STATISTICS_LEVEL to ALL.
[TYPICAL is the default STATISTICS_LEVEL setting recommended by Oracle. Setting the STATISTICS_LEVEL parameter to ALL instructs Oracle to collect additional information on timed OS statistics and plan execution statistics. This information is gathered (by default) every 5 seconds, and can add significantly to the target system's CPU usage and system load.]
Step Elapsed Times (Chart)
For each row source in the Plan Step Statistics grid, Step Elapsed Times displays the elapsed time taken to return the row set from the source.