5.11. Visual Explain Plan

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 similtaneous 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 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, Microsoft SQL Server and Sybase. 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

• 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