Optimization of SQL statements using explain tools to optimize DB2 Performance

The following article describes how to use an interpretation tool to optimize SQL statements in DB2, in practice, interpretation tools are generally used to display the query access plan used by the query optimizer to run an SQL statement.

It contains information that is used to run SQL statements about a wide range of related operations, such as planned operations, their arguments, execution sequence, and costs. Query access plans are one of the most important factors in query performance. To diagnose query performance problems, it is very important to understand the output of interpretation tools.

The explanatory information is usually used:

Understand why the DB2 optimization performance of the application has changed

Evaluate performance tuning

Analyze performance changes

To help you understand the cause of performance changes, you need to explain the information before and after optimization. You can perform the following steps to obtain them:

Capture the query explanation information and save the explanation table before you make any changes. In addition, you can save the output of the db2exfmt interpretation tool. However, for more mature analysis, saving the interpretation information in the interpretation table makes it easier to use SQL queries, and provides significant advantages in maintaining the data stored in the relational DBMS. In addition, the db2exfmt tool can run at any time.

If you do not want or cannot access Visual Explain to view the information, save or print the statistics of the current cataloguing. You can also use the db2look productivity tool to execute this task. In addition, if you use DB2 9.5, you can collect and interpret snapshots when the statement is interpreted. The Db2exfmt tool will automatically format the information contained in the snapshot.

This is especially important when using automatic or statistical information collection, because the statistics used by the Query Optimizer may not be stored in the system cataloguing table, or they may be changed when the statement gets information from the system catalog table to be interpreted.

Save or print data definition language DDL) statements, including the create table, create view, create index, and create tablespace statements. Db2look can also execute this task.

The information collected by this method provides a reference point for future analysis. For dynamic SQL statements, you can collect this information when you run your application for the first time. You can also collect this information when binding static statements. It is very important to collect this information before a major system change, such as installing a new service level or DB2 version or a major configuration change, such as adding or deleting database partitions and distribution data.

This is because such system changes may cause adverse changes to the access plan. Although there should be very few degradation of the access plan, there is such available information that will allow you to solve the problem of DB2 optimization performance degradation faster. To analyze a performance change, compare the previous information with the information about the query and environment collected during the analysis.

In a simple example, your analysis may show that indexes are no longer used as part of an access plan. When Visual Explain or db2exfmt is used to display the cataloguing statistics, you may notice that the number of index levels is much higher than the value when the first query was bound to the database. Then you can perform the following operations:

Reorganize Indexes

Collect new statistics for your tables and Indexes

Collect explanation information when rebinding.

After you perform an operation, check the query plan. If the index is used again, the query performance may no longer be a problem. Repeat these steps until the problem is resolved.

Evaluate performance adjustment results

You can perform a series of operations to help improve query performance, such as checking configuration parameters, adding containers, and collecting and refreshing cataloguing statistics.

You have made changes in these aspects. If there are changes in the selected aspects of the access plan, you can use the interpretation tool to determine the impact. For example, if you add an index or materialized query table MQT Based on the index guide, interpreting the data can help you determine whether to index or materialized query the table to be used as expected.

Although the explain output provides information and costs that let you determine the selected access plan, the only way to accurately measure DB2 optimization performance improvement for a query is to use the benchmark technology.