Visual Explain Plan (Revision #8)

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 window. The visual explain plans have an identical layout for most 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 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 LUW explain implementations will create explain data in the explain tables identified by a Globally Unique Identifier (GUID) and a statement id to ensure concurrency.

ADS supports execution plans for Oracle, DB2 LUW, DB2 z/OS, Microsoft SQL Server, MySQL, PostgreSQL, Redshift, Greenplum, ParAccel, Teradata, Teradata Aster, Netezza, Sybase and Hive, Spark and Impala distributions. Oracle and DB2 support explain plans through explain tables, and Microsoft SQL Server supports them through returned resultsets.

• To view a diagram of the Explain Plan, select Explain Diagram.
• To save column positions within the Execution Plan, select Save Column Positions so that re-execution 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:

Amazon Redshift

• Operation - Plan nodes that refer to the execution on compute nodes. Alert messages from the database will also be displayed in this column. For joins it also specifies a method for how data will be moved around a cluster to facilitate the join. This data movement can be either a broadcast or a redistribution. It also references inner and outer tables. Additional information can be found here: http://docs.aws.amazon.com/redshift/latest/dg/c-the-query-plan.html
• Additional Operation Info - Qualifier and filter information for the operation.
• *Total Cost - The estimated relative cost of executing the step.
• *Startup Cost - The estimated relative cost of returning the first row for this step.
• Rows Produced - The estimated number of rows that will be produced by the step.
• Bytes Produced - The estimated number of bytes that will be produced by the step. 
• Width - Number of bytes per row.
• Maximum Time(uS) - Maximum amount of time for the step(s) to execute (in microseconds). 
• Maximum Time(uS) - Maximum amount of time for the step(s) to execute (in microseconds). 
• Average Time(uS) - Average time for the step(s) to execute (in microseconds). 
• % of Total Max Time - Percentage of the total maximum amount of time for all steps.
• % of Total Avg Time - Percentage of the total average amount of time for all steps.
• Work Memory - Amount of working memory (in bytes) assigned to the query step(s). 
• Rows Pre-filtered - For scans of permanent tables, the total number of rows emitted before filtering rows marked for deletion (ghost rows).
• Step Labels -  Consists of the query step name(s) and, when applicable, table ID and table name. A list of step labels and their associated function can be found here: http://docs.aws.amazon.com/redshift/latest/dg/query-plan-summary-map.html
• Step Location - Location where the step executes, compute node or leader node. The leader node receives the query and parses the SQL. The compute node slices and executes the query segments in parallel. When the compute nodes are done, they return the query results to the leader node for final processing.
• QueryNo - The number assigned to this query.

DB2 LUW

• 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

DB2 z/OS (9.1, 10.1, 11.1)

• 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.

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

Explain Plans for Redshift, ParAccel, Teradata, Teradata Aster, Netezza

These databases display a text based Explain which means that the output for the execution plan will not be in grid format like the databases listed above. The execution plan will display query cost, table scan or index related access, join information and sort information. Each database might have the output layout a little different but the content should be similar. The content that is displayed is based on what the database server returns to Aqua Data Studio.

Explain Plans for PostgreSQL and Greenplum

Both PostgreSQL and Greenplum database display a text based Explain. The execution plan will display query cost, table scan or index related access, join information and sort information. Each database might have the output layout a little different but the content should be similar. The content that is displayed is based on what the database server returns to Aqua Data Studio.

Under File->Options->Visual Explain you can enable Explain Analyze for Greenplum and PostgreSQL. When this option is enabled, the ANALYZE option is turned on and causes the statement to be actually executed, not only planned. The actual run times and other statistics are displayed inside Show Execution Plan or Execute Explain. This parameter defaults to OFF.

Enable Explain Analyze = OFF

For PostgreSQL and Greenplum with the default setting (File > Options > Visual Explain > Enable Explain Analyze = OFF)- When "Execute Explain" is invoked in Query Analyzer, EXPLAIN (ANALYZE off ... ) is used. Since ANALYZE is off, the query will not be executed and hence the database will display only a basic explain plan. If the Query > Show Execution Plan tab is enabled and a query is executed in Query Analyzer, then EXPLAIN (ANALYZE off ... ) is used. The query results will display in the Grid or Text results tab and a basic explain plan will display in the Execution Plan tab.

Enable Explain Analyze = ON

For PostgreSQL and Greenplum with File > Options > Visual Explain > Enable Explain Analyze = ON - When "Execute Explain" is invoked in Query Analyzer, EXPLAIN (ANALYZE on ... ) is used. Since ANALYZE is on, the query will be actually executed and ADS will display a full explain plan in the Execution Plan tab. When the Query > Show Execution Plan tab is enabled and a query is executed in Query Analyzer, then EXPLAIN (ANALYZE on ... ) is used. The query results will display in the Grid or Text results tab and ADS will display a full explain plan in the Execution Plan tab. This Execution plan will have more actual query costing information in the explain plan since the query was executed.

Important: Keep in mind that the statement is actually executed when the ANALYZE option is used. That means that the statement will be executed twice if the Query > Show Execution Plan is activated. Once to do the explain analysis and once to get the result set. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual. Executing EXPLAIN ANALYZE on an INSERT, UPDATE, DELETE, CREATE TABLE AS, or EXECUTE statement will affect your data.

Explain Plans for Hive, Impala and Spark

These databases display output as a text-based explain which means that the output for the execution plan will not be in grid format as some of the other databases. The text output displays the different stages of the query plan which can include map/reduce, file system and metastore. Additionally, networking, work distribution, join information and costing statistics will also be displayed depending on the database and the clause/level specified in File > Options. Since the output displayed in Aqua Data Studio comes directly from the database, the output format will be different for each database, as each has different methods for query optimization. Hive and Spark databases also have an optional explain suffix which varies the content of the output. For Impala, a set command option changes the output format level. These options are available in Aqua Data Studio and are explained below. These options are persisted therefore any subsequent explain will also use that option.

Explain Plan options for Hive, Impala and Spark

In Aqua Data Studio under File > Options > Visual Explain, you can add an optional suffix to the explain clause for Hive and Spark or change the set level for Impala. 

For Hive Visual Explain, the suffix can be one of the following:

• NONE - Basic explain.
• EXTENDED - Produces extra information about the operators in the plan.
• DEPENDENCY - Produces extra information about the inputs in the plan.
• AUTHORIZATION - Shows all entities needed to be authorized to execute the query and authorization failures if any exist.

For Impala Visual Explain, these are the levels that can be set:

• 0 - MINIMAL - Basic explain. One line per operation.
• 1 - STANDARD  - The default level of detail showing the logical way that work is split up for the distributed query.
• 2 - EXTENDED - Includes additional detail about how the query planner uses statistics in its decision making process.
• 3 - VERBOSE - The maximum level of detail showing how work is split up within each node into "query fragments" that are connected in a pipeline.

For Spark Visual Explain, the suffix can be one of the following:

• NONE - Basic explain.
• EXTENDED - Produces extra information about the logical plan before and after analysis and optimization.

Explain Plan for Sybase 12.5,  15.x and 16.x
When executing a query, it’s possible to view Execution Plan for Sybase 12.5,  Sybase 15.x and Sybase 16.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. The query will not be executed when the "Execute Explain" button is used.

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. The query will be executed when the "Execute Explain" button is used.  

Important: Keep in mind that executing an explain in the XML Explain mode on an INSERT, UPDATE or DELETE statement will affect your data.

• 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.