ADS provides a "Show Execution Plan" option in the main toolbar. If this option is enabled an execution plan will be generated for each query executed in the results tab. Multi-execution plans are supported, thus executing a script will generate multiple execution plans. ADS also provides an "Execute Explain" button in the query window which will generate an execution plan for the current query or the highlighted queries 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.

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. You may change the settings in the File->Options->Explain tab.

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 that database.

Visual Explain Plan