Previous Topic

Next Topic

Book Contents

Book Index

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, 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:


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.

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.


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:

Run DB2 Index Advisor and Run DB2 Explain Tool

These buttons appear for DB2/UDB.




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.

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:

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:

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.


query name



standard query

The normal query used to display the plan information in a meaningful way.



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.


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.



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.