Chat now with support
Chat with Support

Spotlight on Oracle 10.7 - 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 Spotlight on MySQL Troubleshooting: Connection Problems

SQL Plan (Full Screen) Page

To open the SQL Plan (full screen) page

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

  2. Click I/O | SQL Page.

  3. Select a SQL Statement in the Top SQL grid.
  4. From any of the sub pages click Full Screen.
  5. Click SQL Plan

Note: Click Restore screen to return to the Top SQL grid.

SQL Plan (Grid)

SQL Plan shows the details of the SQL execution plan chosen by the Oracle query optimizer for the specified SQL statement.


  • 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


The operation performed in each step of the plan.


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.


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.


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

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.




The control structure within the step that produces the row set.


The number of times the step was performed.


The optimizer's estimate of the cost of the step.

Time (ms)

The time taken to return a row set.


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