Easily find issues by searching: #<Issue ID>
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
Aqua Data Studio / nhilam
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.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. When enabled, coloring of operation costs mark bottleneck issues.
The Explain Plan grid has several right click pop-up menu options for viewing the results in different ways and highlighting areas of interest:
The Explain Plan grid provides the following vendor-specific information regarding each operation:
*Cost is a measure that compares the relative execution times of the steps within a plan. Cost does not provide any precise information about actual execution times or memory consumption, nor does it provide a meaningful comparison between execution plans. It does give you an indication of which operations in a query are consuming the most resources.
See Also: Redshift Visual Explain
DB2 z/OS (9.1, 10.1, 11.1)
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:
For Impala Visual Explain, these are the levels that can be set:
For Spark Visual Explain, the suffix can be one of the following:
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.
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.
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.The Details tab displays information about the currently selected node in the Diagram, including information on
The XML tab displays the full XML document created by the server of the Explain.The Advanced tab displays:
Execution Plan for Sybase IQ 15.x and Sybase IQ 16.0
When executing a query it is possible to view Execution Plans for Sybase IQ 15.x and Sybase IQ 16.0. Once the query executes and the Execution Plan Results Tab is viewed, there are three possibilities for viewing:
The Query Plan displays a diagram which allows clicking nodes to view details for the clicked node in the Details tab.The Query Timing Chart displays a color-coded HTML table of the Query Timing with Elapsed Time, Threads, CPU use and Wall Time. The Query Timing Chart offers a color-coded Timing Legend to indicate the elapsed time within each operation for:
Query Text displays server version information and the content of the query being executed.