Visual Explain Plan (Revision #1)

Aqua Data Studio’s Visual Explain provides visual display of an execution plan for a query, allowing for query tuning to enhance query and server performance.

ADS provides a "Show Execution Plan" option in the Main Application Toolbar. If this option is enabled an execution plan will be generated for each query executed in the results Execution Plan tab. Multi-execution plans are supported and executing a script will generate multiple execution plans. Aqua Data Studio also provides an "Execute Explain" button in the query window which will generate an execution plan for the current query or the highlighted queries in the Query Analyzer without executing the query. The visual explain plans have an identical layout for all databases except for the row information for each node operation, which contains specific column information according to the specific database server and version.

There are 2 ways that ADS can be used in comparing execution plans of queries. The first option is to "Execute Explain" on 2 highlighted queries at the same time, which will give you 2 visual explains in the results to compare. The second option is to open 2 popup Query Windows and execute a single query in each window to be compared side-by-side.

Before you begin using visual explain you may want to review the default settings according to your database vendor and version. You may change the settings in the File->Options->Explain tab.

It is also possible to add execution plans to the Visual Explain Whiteboard. Right Click on the contents of the Execution Plan to add that Plan to the Explain Whiteboard for quick comparison of execution time from multiple queries in one window.

Explain Diagrams can be generated from Execution Plans and saved as JPEG or PNG images for sharing. Each operation's details can be viewed and the color of critical operations can be altered to assist in highlighting important bottlenecks and steps in the execution process. Diagram objects can be repositioned to create meaningful images for others.

Microsoft SQL Server does not have any configurable settings and works from installation by only enabling the execution plan. For Oracle, the default settings should also work from installation. ADS will create and drop an explain table every time the user executes a query or executes explain with a dynamic explain table name using the session id, so the explain tables are not confused by simultaneous users. If you would like to manually configure and create an explain table for all of your users, you may do so and have all ADS users configure their ADS installation to access that specific table. DB2 LUW should also work out of the box, which by default will create the explain tables if they do not exist but will not drop them. The reason for the different configurations for the 3 different databases is because of the different implementations by the database vendor. Microsoft SQL Server’s explain plans can be easily enabled by issuing an SQL statement which will return explain plan resultsets. Oracle allows a user to execute an EXPLAIN statement which provides an option to place the explain data in an explain table of the user’s choice. DB2 also provides an EXPLAIN statement which will only insert data into a defined explain plan table. The issues arise in supporting explain plans for concurrent users. The ADS Oracle and DB2 explain implementations will create explain data in the explain tables identified by a Globally Unique Identifier (GUID) and a statement id to ensure concurrency.

Currently, ADS supports execution plans for Oracle, DB2 LUW, DB2 z/OS, Microsoft SQL Server, Sybase, MySQL, PostGreSQL, Redshift, Greenplum, ParAccel, Teradata Aster and Netezza. The codebase is in place to support execution plans for any database which supports explain plans through the retreival of a resultset. Oracle and DB2 support explain plans through explain tables, and Microsoft SQL Server supports them through returned resultsets. If other databases include this type of support, ADS can easily be made to support visual explains for those databases.

• To view a diagram of the Explain Plan, select Explain Diagram
• To save the grid’s column widths within the Execution Plan, select Save Column Positions so that reexecution of the query keeps statistics as currently arranged.
• To add this Explain Plan to the Explain Whiteboard for comparison, select Add to Whiteboard
• To highlight the critical node, select Color Critical Node. This will enable coloring of critical nodes to highlight execution costs.
• To highlight the critical operation, select Color Critical Operation

What the Explain Plan displays varies depending on the database and vendor. Sybase 12.5 and Sybase 15.x's Explain Plan feature is discussed at the bottom of the page. Sybase IQ 15.x and Sybase IQ 16.0's Execution Plans are also described. 

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

• 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

• 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

• Cardinality - The total composite cardinality. (from Explain table DSN_DETCOST_TABLE and column COMPCARD)
• Total Cost - The total composite cost. (from Explain table DSN_DETCOST_TABLE and column COMPCOST)
• CPU Cost - The total composite CPU cost. (from Explain table DSN_DETCOST_TABLE and column COMPCPUCOST)
• IO Cost - The total composite I/O cost. (from Explain table DSN_DETCOST_TABLE and column COMPIOCOST)
• Filter Fact Match - The filter factor of matching predicates only. (from Explain table DSN_DETCOST_TABLE and column IMFF)
• Filter Fact Match/Screen - The filter factor of matching and screening predicates. (from Explain table DSN_DETCOST_TABLE and column IMFFADJ)
• Qualified Rows - The number of rows qualified after applying local predicates. (from Explain table DSN_DETCOST_TABLE and column ONECOMPROWS)
• Join Type - The type of join used. (from Explain table PLAN_TABLE and column JOIN_TYPE)
• Join Method - The join method used. (from Explain table PLAN_TABLE and column METHOD)
• Merge Join Cols - The number of columns that are joined during a merge scan join. (from Explain table PLAN_TABLE and column MERGE_JOIN_COLS)
• Obj Access Type - The method of accessing the new table. (from Explain table PLAN_TABLE and column ACCESSTYPE)
• Obj Access Creator - The creator of the index - only used for index accesstype. (from Explain table PLAN_TABLE and column ACCESSCREATOR)
• Obj Access Name - The name of the index - only used for index accesstype. (from Explain table PLAN_TABLE and column ACCESSNAME)
• Obj Type - The type of the new table. (from Explain table PLAN_TABLE and column TABLE_TYPE)
• Obj Creator - The creator of the new table. (from Explain table PLAN_TABLE and column CREATOR)
• Obj Name - The creator of the new table. (from Explain table PLAN_TABLE and column TNAME)
• Obj Join Location - The location of the table relative to the join. Inner or Outer. (Calculated in ADS)
• Prefetch - Indication of whether data pages are to be read in advance by prefetch. (from Explain table PLAN_TABLE and column PREFETCH)
• Index Only - Indication of whether access to an index alone is enough to perform the step. (from Explain table PLAN_TABLE and column INDEXONLY)
• Index Matching Columns - The number of index keys that are used in an index scan. (from Explain table PLAN_TABLE and column MATCHCOLS)
• Sort New Unique - Indication of whether the new table is sorted to remove duplicate rows. (from Explain table PLAN_TABLE and column SORTN_UNIQ)
• Sort New Join - Indication of whether the new table is sorted for join. (from Explain table PLAN_TABLE and column SORTN_JOIN)
• Sort New Orderby - Indication of whether the new table is sorted for ORDER BY. (from Explain table PLAN_TABLE and column SORTN_ORDERBY)
• Sort New Groupby - Indication of whether the new table is sorted for GROUP BY. (from Explain table PLAN_TABLE and column SORTN_GROUPBY)
• Sort Comp Unique - Indication of whether the composite table is sorted to remove duplicate rows. (from Explain table PLAN_TABLE and column SORTC_UNIQ)
• Sort Comp Join - Indication of whether the composite table is sorted for join. (from Explain table PLAN_TABLE and column SORTC_JOIN)
• Sort Comp Orderby - Indication of whether the composite table is sorted for an ORDER BY clause or a quantified predicate. (from Explain table PLAN_TABLE and column SORTC_ORDERBY)
• Sort Comp Groupby - Indication of whether the composite table is sorted for a GROUP BY clause. (from Explain table PLAN_TABLE and column SORTC_GROUPBY)
• Query Block Type - For each query block, an indication of the type of SQL operation that is performed. (from Explain table PLAN_TABLE and column QBLOCK_TYPE)
• Query No. - A number that identifies the statement that is being explained. (from Explain table PLAN_TABL and column QUERYNO)
• Query Block No. - A number that identifies each query block within a query. (from Explain table PLAN_TABLE and column QBLOCKNO)
• Parent QBlock No. - A number that indicates the QBLOCKNO of the parent query block. (from Explain table PLAN_TABLE and column PARENT_QBLOCKNO)
• Plan No. - The number of the step in which the query that is indicated in QBLOCKNO was processed. This column indicates the order in which the steps were executed. (from Explain table PLAN_TABLE and column PLANNO)
• Appl Name - The name of the application plan for the row. (from Explain table PLAN_TABLE - APPLNAME)
• Program Name - The name of the program or package containing the statement being explained. (from Explain table PLAN_TABLE and column PROGNAME)
• Explain Time - The time when the EXPLAIN information was captured. (from Explain table PLAN_TABLE and column EXPLAIN_TIME)
• Cost Category - Indicates if DB2 was forced to use default values when making its estimates. (from Explain table DSN_STATEMNT_TABLE and column COST_CATEGORY)
• Cost Category - Reason for using default - A string that indicates the reasons for using Default values. (from Explain table DSN_STATEMNT_TABLE and column REASON)
• Statement - The SQL Statement which was EXPLAINed (Calculated in ADS)

MySQL

• Total Query Cost - The total cost of the last compiled query as computed by the query optimizer
• SQL Statement - The SQL statement being explained.
• ID - The select identifier. This is the sequential number of the select within the query.
• Select Type - The select statement type.
• Table - The name of the table to which the row of output refers.
• Partitions - The partitions from which records would be matched by the query.
• Type - The join type.
• Possible Keys - The possible indexes to choose.
• Key - The key column indicates the key (index) that MySQL actually decided to use.
• Key Length - The length of the chosen key.
• Ref - The columns compared to the index.
• Rows - Estimate of the rows to be examined.
• Filtered - The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. 
• Extra - This column contains additional information about how MySQL resolves the query.

Explain Plan for Sybase 12.5 and 15.x 
When executing a query, it’s possible to view Execution Plan for Sybase 12.5 and Sybase 15.x as TEXT or XML based on the type selected in File > Options > Visual Explain > Sybase ASE Visual Explain Plan. Explain Plans are available by executing an explain by clicking the "Execute Explain Plan" button in the Query Analyzer toolbar, and by enabling "Show Execution Plan" for Results in the Main Application Toolbar when running a query in a regular fashion.  

TEXT Explain

The TEXT Explain option displays a text formatted version of the explain plan, with an associated Explain grid underneath it. There are columns within the grid for Total Logical Reads, Total Physical Reads, Scan Count, Regular Reads Logical, APF Reads Logical, Regular Reads Physical, APF Reads Physical and APF IOs.

XML Explain

With the XML Explain option selected, the Explain Plan displays a diagram which allows clicking nodes to view details for those nodes in the Details tab. Mouse hover over nodes also produces a tool-tip containing statistics for that node.

• Node Statistics
• Subtree Statistics
• Details for IndexScans
• Partition Information

The XML tab displays the full XML document created by the server of the Explain.

• Plan Information
• Abstract query plan 
• Resource utilization
• Cost
• Optimizer Metrics
• Optimizer Statistics

• Query Plan
• Query Timing Chart
• Query Text

The Query Plan displays a diagram which allows clicking nodes to view details for the clicked node in the Details tab.

• Condition Execution 
• Prepare 
• 1st Fetch 
• Subsequent Fetches 
• Complete 

Query Text displays server version information and the content of the query being executed.