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’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.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:
What the Explain Plan displays varies depending on the database and vendor. Sybase 12.5 and Sybase 15.0's TEXT feature is discussed at the bottom of the page. The Explain Plan grid provides the following vendor-specific information regarding each operation:
Explain Plan for Sybase 12.5 and 15.0 - Text
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.