立即与支持人员聊天
与支持团队交流

Spotlight on Oracle 10.10 - Release Notes

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

  1. Select the Spotlight on Oracle connection in the Spotlight Browser.

  2. Click SQL & Application Workload | Top SQL Page.

  3. Select a SQL Statement in the Top SQL grid.
  4. 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

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.

 

Related Topics

相关文档

The document was helpful.

选择评级

I easily found the information I needed.

选择评级