Explain Plan

AQT can display the Explain Plan for an SQL statement. The Explain Plan shows you information about the way the database will process your SQL statement. This information is useful for analysing and improving the performance of your queries.

To show the Explain Plan, in the Run SQL window, select Run > Explain SQL or hit F8. If you wish to see your existing Explain Plans, without Explaining the SQL, select Window >Explain Plan.

This feature is available for Oracle, DB2 z/OS, DB2/UDB, SQL Server, Sybase, MySQL, IDMS and Teradata.

For MySQL and SQL Server, the Explain-plan information is shown as a normal query display.

For Sybase, the Explain-plan information is shown in a pop-up box. The Sybase ODBC Driver returns the explain data to AQT in a strange order. This makes the display difficult to use, especially if you have a complex query. There is little we can do to correct this issue.

Explain Plan for Oracle and DB2

For Oracle and DB2 a special Explain Plan window is displayed.

Plan / Explain Tables needed

For some databases, in order to use the Explain Plan feature, you will need to have Plan or Explain tables set up for you. If you do not have these you will get the message PLAN_TABLE not found (or similar) when you try to Explain your SQL.

To create the Plan / Explain tables, contact your DBA.

For DB2/UDB, the DDL for the Explain tables are in %sqllib%\misc\explain.ddl.
If you are unable to locate the Explain table DDL, email support and we can send you DDL for the plan/explain tables. Please tell us what database type and database version you are using - otherwise we'll just have to email you back and ask you. ;)

DB2/UDB and SYSTOOLS

For DB2/UDB, explain tables can be written to the SYSTOOLS schema rather than your own schema. DB2 will automatically use the SYSTOOLS explain tables if they do not exist under your userid.

Similarly, if AQT finds the explain tables under your userid, it will use them in the explain displays. Otherwise it will show information from the SYSTOOLS explain tables.

Explain Plan window

The Explain Plan window contains the following:

Plan

The combo-box and buttons allow you to select the plan you wish to display. By default the most recent plan is shown, however you may select another plan.

Only plans generated by AQT will be seen here.

Plan Identifier

AQT will compose a unique identifier for the Plan when it does an Explain Plan.

For Oracle this is aqt plus a timestamp – example aqt20020526155411.
For DB2 z/OS this will be a timestamp – example 20020526155411. To distinguish AQT plans from other entries in your plan table, AQT plans use a Queryno of 678.

AQT will not automatically delete plans, but will retain them in your PLAN_TABLE until you choose to delete them.

Delete this Plan

This button will delete the existing plan.

Delete all Plans except this

This button allows you to purge all plans except the one currently shown.

Only plans generated by AQT will be deleted – other plans will be left untouched.

Cost

This will show you the Cost estimate of your SQL statement. This will only be shown for Oracle and DB2/UDB.

For Oracle, this will be blank if Oracle has decided to do rules-based optimization instead of cost-based optimization. Oracle will use rules-based optimization in two circumstances:

your database has been configured to always use rules-based optimization
the tables in your query do not have any statistics (statistics are generated when you run an ANALYZE on your tables).

Run DB2 Index Advisor and Run DB2 Explain Tool

These buttons appear for DB2/UDB.

function

action

explanation

Run DB2 Index Advisor

runs program db2advis

DB2 Index Advisor is a very valuable tool for recommending indexes that may improve the processing of your query.

In order to use the Index Advisor, you need to create some extra Explain tables (ADVISE_WORKLOAD and ADVISE_INDEX).

Run DB2 Explain Formatter

runs program db2exfmt

DB2 Explain Tool is an “old” text-based version of the Visual Explain, however it can sometimes provide useful information.

These functions will only work if the db2advis and db2exfmt programs are installed on your PC and they are in your PATH. These programs are only installed with some types of the DB2 Client and not in others.
Run Index Advisor and Run Explain Formatter can not be used if you specify Options > User Rights > Disable Save Password. If you try to do so you will get message For security reasons, this function can not be used. See Disable Save Password for more on this.
You can customize the commands that AQT uses for invoking these functions. You do this by changing the db2advis and db2expl entries at the bottom of the db2udb.cfg file (this file is in your AQT directory).

Plan display grid

The grids in this pane show the information about the Plan. Your database documentation explains the contents of this pane.

AQT has a number of different plan queries that can be shown here – see the section Query below.

The Plan display is shown in two grids:

the Main grid shows the multiple lines of the explain plan
the Detail grid which shows the detail of an individual row in the Main grid

As you select entries in the Main grid, the data shown in the Detail grid will change.

The Detail grid can be resized if required, and hidden with View >Detail Grid.

SQL text

AQT will also show you in the bottom pane the SQL statement that the Plan is based on. The SQL text is not present in the Explain_Table; instead, this information comes from the AQT SQL Statement history.

It is possible that the SQL statement cannot be found in the SQL statement history (which contains only the last 200 SQL statements you have run). In this case the SQL text pane will be blank.

For DB2/UDB the Text frame provides three options to determine the SQL display:

SQL (your SQL query),
Optimised SQL (the SQL as “rewritten” by the DB2 optimiser)
Comments (any free-format comments you may have added to your explain). You can add/change any comments by typing them into the text box and clicking on Set.

Query listbox

AQT has a number of different ways to display the Plan Information; select the one you wish to use from this listbox.

These queries are different for different database types; they are specified in the AQT cfg file and so can be added / amended if required. Up to 5 explain-queries can be specified.

Database	query name	description
Oracle	standard query	The normal query used to display the plan information in a meaningful way.
	V7	If you are running Oracle V7, or have an “old” structure of your Plan_Table, select V7. This uses a simpler query with fewer Plan_Table columns.
	full explain table	Shows the full contents of the explain table, which can be useful extra information.
DB2 z/OS	standard query	The normal query used to display the plan information in a meaningful way.
	full explain table	Shows the full contents of the explain table, which can be useful extra information.
DB2/UDB	stream (simple)	Shows the order of processing the SQL in a simple (but fast) query.
	stream (complex)	This is a more sophisticated version of the stream query, and is probably the most useful. However it is a very complex query and may take a long time to run, especially when you are analysing a complex query.
	objects	shows the objects used in the query
	plan info	shows the full information in the Explain_Instance table

DB2/UDB comes with some very good tools for analysing plan information, in particular the Visual Explain tool which is available in the Control Center. AQT’s plan analysis is not designed to replace this tool, but to provide a quick display of basic plan information. If you have a complex query, it is recommended that you use Visual Explain rather than AQT.

DB2/UDB holds plan information in a very complex way that is difficult to display simply. The stream queries can seem very cryptic. A visual representation of this information (such as in Visual Explain, or DB2 Explain Tool) is generally easier to understand.

Explain Plan for DB2/400

This is not possible, as DB2/400 does not have an Explain Plan command that can be issued through SQL.

Instead you will have to use the PRTSQLINF CL command or the iSeries Navigator Visual Explain tool.

More on Explain Plan for DB2 z/OS

By default, AQT runs the Explain with the command explain plan set queryno=<queryno> for <sql> where <sql> is your SQL statement and <queryno> is the your query number (by default 678).

You can customize the command AQT uses for running the explain. This is done with an entry in the cfg file (db2390.cfg) - see the explain entries near the bottom.

Some users prefer to run the explain using the SYSPROC.DSNAEXP stored procedure rather than Explain Plan. SYSPROC.DSNAEXP provides more flexibility, for instance it allows the explain to be written to a generic / shared Plan Table. To implement the use of SYSPROC.DSNAEXP, edit the db2390.cfg file and customize / uncomment one of the explain entries.

If you are using SYSPROC.DSNAEXP, and are using a PLAN_TABLE other than your own, you must also specify to AQT where this PLAN_TABLE resides. This is done with Options > Explain Plan > Plan table Schema. AQT will use this schema in the queries it uses for displaying the Explain information.

More details are provided in Customizing the Explain Statement Used.