Visual Explain Plan - Explain Plan

The Visual Explain Plan offers insight into the execution of SQL Statements and helps the user isolate potential performance bottlenecks. Each operation (Join, Sort, Index Scan, etc) is displayed with statistics about the resources required to perform the task.

The Explain Plan grid has several pop-up menu options to view the results in different ways and highlight areas of interest:

To view a diagram of the Explain Plan, select Explain Diagram
To save the grid’s column widths, select Save Column Positions
To add this Explain Plan to the Explain Whiteboard for comparison, select Add to Whiteboard
To highlight the critical path, select Color Critical Path
To highlight the critical node, select Color Critical Node
To highlight the critical operation, select Color Critical Operation

The Explain Plan grid provides the following vendor-specific information regarding each operation:

Oracle

Operation - operation name
Node Cost - total estimated cost of the operation
IO Cost - estimated I/O cost of the operation
CPU Cost - estimated CPU cost of the operation
Cardinality - estimated number of rows accessed by the operation
Bytes - estimated number of bytes accessed by the operation
Position - for 1st row of output: the optimizer’s estimated cost of the statement; for other rows: the position relative to other children of the same parent
Obj Owner - name of the user who owns the schema containing the table or index
Obj Name - name of the table or index
Options - an operation descriptor
Obj Type - descriptive information about the type of object
Optimizer - current mode of the optimizer
Obj Instance - ordinal position of the object as it appears in the original SQL statement
Remarks - A comment (up to 80 bytes) associated with each step of the explain plan
Obj Node - Name of the database link used to reference the object
Search Columns - Not currently supported
Other Tag - Describes the contents of the Other column
Partition Start - the first partition in the range of partitions accessed
Partition Stop - the last partition in the range of partitions accessed
Partition Id - step that determines first & last partitions
Other - Miscellaneous information regarding this operation
Distribution - the method used to distribute rows from producer to consumer servers
Temp Space - estimated temporary space, in bytes, used by the operation

SQL Server

Operation - operation name
Subtree Cost - cost of this node in addition to the costs of any child nodes
Node Cost - total estimated cost of the operation
Est. IO - estimated IO cost
Est. CPU - estimated CPU cost
Est. Rows - estimated number of rows accessed by the operation
Rows - number of rows produced by the operation
Executes - number of executions of this operation
Argument - arguments sent to this operation
Type - Type of node
Average Row Size - average row size (in bytes)
Defined Values - values defined by this operation
Output List - output values of this operation
Warnings - warning messages for this operation
Parallel - 0 / 1 indicating if this operation is running in parallel
Est. Executions - estimated number of executions of this operation
Physical Operation - Physical / Implementation operation name
Logical Operation - Logical / Algebraic operation name
Statement Text - complete operation name with arguments

DB2

Operation - operation name
Subtree Cost - cost of this node in addition to the costs of any child nodes
Node Cost - total estimated cost of the operation
IO Cost - estimated cumulative I/O cost of executing the operation
CPU Cost - estimated cumulative CPU cost of executing the operation
Obj Schema - name of the schema containing the object
Obj Name - object name
First Row Cost - estimated cumulative cost of fetching the first row for this operation
RE Total Cost - estimated cumulative cost of fetching the next row for this operation
RE IO Cost - estimated cumulative I/O cost of fetching the next row for this operation
RE CPU Cost - estimated cumulative CPU cost of fetching the next row for this operation
Commun. Cost - estimated cumulative communication cost of executing this operation
First Commun. Cost - estimated cumulative communication cost of fetching the first row for this operation
Remote Commun. Cost - estimated cumulative remote communication cost for this operation
Remote Total Cost - estimated cumulative total cost of executing this operation on remote databases
Stream Count - estimated cardinality of data stream
Buffers - estimated buffer requirements for this operation and its inputs
Column Count - number of columns in data stream
Explain Requester - authorization id of initiator of this Explain request
Explain Time - time of initiation of Explain request
Source Name - name of the package running or source file when the statement was explained
Source Schema - Schema, or qualifier, of source of the explain request
Explain Level - level of explain information for this row
Section Number - section number within package
Stream ID - unique id for this data stream
Source Type - indicates the source of this data stream
- O - Operator
- D - Data Object
Target Type - indicates the target of this data stream
- O - Operator
- D - Data Object
Column Names - names and order of the columns in this stream
PMID - partitioning map id
Single Node - indicates if this data stream is on a single or multiple partitions
- MULT - on multiple partitions
- COOR - on coordinator node
- HASH - directed using hash
- RID - directed using row id
- FUNC - directed using a function: HASHEDVALUE() or DBPARTITIONNUM()
- CORR - directed using a correlation value
- numeric - directed to the predetermined single node
Partition Columns - list of columns on which this data stream is partitioned
Operator Type - descriptive label of the type of operation

Visual Explain Plan

Explain Plan Features
When executing a query, it’s possible to view Execution Plan for Sybase 12.5 and Sybase 15.0 as TEXT. The feature is available both as viewing Execution Plan only and in the tab Show Execution Plan when running the query in a regular fashion.

Sybase Explain Plan