Administration Guide

Using the SQL Explain Facility

The different means of capturing explain information include using:

1. EXPLAIN and EXPLSNAP BIND/PREP options
2. CURRENT EXPLAIN MODE and CURRENT EXPLAIN SNAPSHOT special registers
3. EXPLAIN SQL statement
4. db2vexp tool (also directly calls Visual Explain to display the information)

There are three reasons you may wish to collect and use explain data:

1. To understand the steps (the access plan) that the Database Manager must perform to satisfy your query. Data Access Concepts and Optimization provides information which you may need to reference if you wish to understand the explain output.
2. To help evaluate your performance tuning initiatives. There are a number of actions you can take to help improve the performance of your queries. Many of these possible actions are described in subtopics of the following:
   • Chapter 22, Application Considerations
   • Chapter 23, Environmental Considerations
   • Chapter 24, System Catalog Statistics.

   After making a change in any of these areas, you can use the SQL explain facility to determine the impact, if any, that the change has on the access plan chosen. For example, if you add an index based on the recommendations provided in Indexing Impact on Query Optimization, the explain data can help you determine whether the index is, in fact, being used as you expected.

   While the explain output will provide you with information to allow you to determine the access plan that was chosen and its relative cost, the only way to accurately measure the performance improvement for a query is to use benchmark testing techniques, as described in Chapter 31, Benchmark Testing.

3. To help you understand the reasons for changes in query performance, you need to have the explain information both before and after your change in order to analyze the impact. Therefore, when compiling a SQL statement to the database, you should:
   • Use the explain facility to capture the plan information before your changes, and save the resulting explain tables; or, save the output from the db2exfmt explain tool.
   • Save and/or print the current catalog statistics if you do not want to, or cannot, access Visual Explain to view this information. (The db2look productivity tool, described in Modeling Production Databases, could be used to help perform this task.)
   • Save and/or print the data definition language (DDL) statements, including those for CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE TABLESPACE.

   The above information provides you with a before picture that you can use as a reference point for future analysis. For dynamic SQL statements, you can also collect this information when you run your application for the first time. For static SQL statements, you can also collect this information at bind time.

   When you wish to analyze the reason for a performance change, you can compare the before data to information you collect about the query and environment when you are starting your analysis (the after data).

   As a simple example, your analysis could show that an index is no longer being used as part of the access path. Using the catalog statistics information in Visual Explain, you might notice that the number of index levels (NLEVELS column) is now substantially higher than when the query was first bound to the database. You might then choose to:

   • Reorganize the index
   • Collect new statistics for your table and indexes
   • Gather explain information when rebinding your query.

   Following these actions, you might notice that the index is once again being used in the access plan and that performance of the query is no longer a problem.