Chat now with support
Chat with Support

Spotlight on Oracle 10.10 - Getting Started Guide

Welcome to Spotlight Install Spotlight Start Spotlight Spotlight on Oracle Spotlight on Oracle Data Guard Spotlight on Oracle RAC Spotlight on Unix Spotlight on Windows Troubleshooting: Connection Problems

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 RAC 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.

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.

Cost The cost of the operation as determined by the query optimizer.

CPU Cost

The optimizer's estimate of the CPU cost of the operation.

id The value assigned to each step of the execution plan.

IO Cost

The optimizer's estimate of the I/O cost of the operation.

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.)

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.

Disk The number of physical disk reads performed in the step.
Gets The number of logical reads (buffer gets) performed 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

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating