|
|
|
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 describing 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 Enhancements
Now, 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 |
|
