Spotlight on Oracle 10.6 - Getting Started Guide

Tools | Explain Plan allows you to determine the execution plan that Oracle applies to a particular SQL statement. Spotlight on Oracle allows you to view graphical representations of the execution plan for SQL statements being executed by a user, or for those identified by the Top SQL Page.

To open the Explain Plan Window

Location	Instruction
From anywhere in Spotlight.	Click Tools \| Explain Plan.
From Drilldown Pages: Top Sessions \| Session Details Page SQL & Application Workload \| Top SQL Page \| SQL Statistics Page SQL & Application Workload \| Top SQL Page \| SQL Details Page	Click .

Location

Instruction

From anywhere in Spotlight.

Click Tools | Explain Plan.

From Drilldown Pages:

Top Sessions | Session Details Page
SQL & Application Workload | Top SQL Page | SQL Statistics Page
SQL & Application Workload | Top SQL Page | SQL Details Page

Click .

Note: To close the Explain Plan Window click Exit Explain .

SQL Text Window

Show the SQL Statement being tested in the Explain Plan window.

Control	Description
The SQL Statement	View, modify or re-enter the SQL statement and then regenerate the Explain Plan.
	Show / Hide the SQL Text Window.
	(Re)generate the Explain Plan for the SQL statement.

Operation Table

The table is a tree structure of the operations required to perform the SQL query.

Notes:

Select Expand to expand all steps in the table.
Select Show Explanations to show an explanatory tool tip when the mouse hovers over an item in the table.
Show column and index data for the database object in the step highlighted in the Operation table.

Column	Description
Operation	The action on the dataset.
Step	The sequence of the actions. The following tables describe some of the common execution steps that you may encounter: Table Access Paths Index Operations Join Operations Set Operations Miscellaneous Operations Aggregation Operations
Object	The object name.
Cost	The cost of this operation. The values in RED in this column represent the most expensive steps—the critical path—in each level of the explain plan. (When multiple steps in the same level are on the critical path, only the first step will be shown in red.)
Rows	The number of processed rows for this operation.
Bytes	The number of bytes processed for this operation.
Other	This shows the operation on the object as being either serial or parallel.

Views of the Explain Plan

Control	Description
	Show column and index data for the database object in the step highlighted in the Operation table. More details about the Describe table are found in the associated text boxes: Describe table: The name of the displayed table. Row count: The total number of rows in this table. Avg. row length: The average row length of this table. Blocks: The total number of blocks occupied by this table. Empty blocks: The total number of empty blocks in this table.
	Show the Explain Plan as a diagram. The critical path through the SQL query is represented by RED lines and RED-bordered icons.
	Show the Explain Plan as text. The critical path through the SQL query is represented by RED text.

Control

Description