Guidelines for using explain information
You can use explain information to understand why application performance has changed or to evaluate performance tuning efforts.
Analysis of performance changes
before and afterexplain information, which you can obtain by performing the following steps:
- Capture explain information for the query before you make any changes and save the resulting explain tables. Alternatively, save output from the db2exfmt explain tool.
- Save or print the current catalog statistics. You could use the db2look productivity tool to help you perform this task.
- Save or print the data definition language (DDL) statements, including CREATE TABLE, CREATE VIEW, CREATE INDEX, or CREATE TABLESPACE.
The information that you collect in this way provides a reference point for future analysis. For dynamic SQL or XQuery statements, you can collect this information when you run your application for the first time. For static SQL and XQuery statements, you can collect this information at bind time. To analyze a performance change, compare the information that you collect with this reference information that was collected previously.
- Reorganize the index
- Collect new statistics for your table and indexes
- Collect explain information when rebinding your query
After you perform one of these actions, examine the access plan again. If the index is being used, query performance might no longer be a problem. If the index is still not being used, or if performance is still a problem, choose another action from this list and examine the results. Repeat these steps until the problem is resolved.
Evaluation of performance tuning efforts
You can take a number of actions to help improve query performance, such as updating configuration parameters, adding containers, collecting fresh catalog statistics, and so on.
After you make a change in any of these areas, use the explain facility to determine what affect, if any, the change has had on the chosen access plan. For example, if you add an index or materialized query table (MQT) based on the index guidelines, the explain data can help you to determine if the index or MQT is actually being used as expected.
Although the explain output enables you to determine the access plan that was chosen and its relative cost, the only way to accurately measure the performance improvement for a specific query is to use benchmark testing techniques.