DB2forIBMi Performance Tuning tool: Use of VisualExplain

DB2forIBMi provides a series of performance tuning tools. VisualExplain is an important one. It graphically displays the access plan generated after the DB2fori database query engine intelligently optimizes Users' Query requests.

DB2 for IBM I provides a series of performance tuning tools, and Visual Explain is an important one. It graphically displays the access plan generated by the DB2 for I database query engine after the user's query request is intelligently optimized.

The bottleneck in query processing can be quickly determined based on Visual Explain, so that we can optimize query requests. This section describes the startup method and information contained in Visual Explain and uses other tuning tools to optimize the performance of query requests.

DB2 for IBM I provides a series of performance tuning tools, and Visual Explain is an important one. It graphically displays the access plan generated by the DB2 for I query engine after the user's query request is intelligently optimized. The bottleneck in query processing can be quickly determined based on Visual Explain, so that we can optimize query requests. This section describes the startup method and information contained in Visual Explain and uses other tuning tools to optimize the performance of query requests.

DB2 for IBM I Performance Tuning Tool

DB2 for IBM I has provided a series of performance tuning tools as early as V5R4 or even earlier. As DB2 for I continues to grow, these tuning tools are constantly improved. Figure 1 shows optimization tools such as Indexes Advisor, SQE Plan Cache, Detailed DB Monitor Data, and Visual Explain. Most of these tools are integrated in System I Navigator. Therefore, you can use these optimization tools as long as you have an iNavigator client.

Indexes Advisor is an index recommendation provided by the optimizer Based on query requests. It is always on and allows users to query system-level, mode-level, or table-level index recommendations. SQE Plan Cache saves all Query plans running in SQE (SQL Query Engine) in a system-level Query Cache, these optimized access plans can be directly used when similar query requests are available in the future, saving the overhead for re-optimization and not bringing any additional overhead to the query performance. SQE Plan Cache Snapshots (SQL Plan high-speed Cache snapshot) provides the function of saving a query Plan as a snapshot to facilitate analysis of a query request in the future, you can also perform analysis based on the comparison between two snapshots. Detailed DB Monitor Data (Detailed SQL Performance Monitor) is used to collect SQL query performance-related Data running on IBM I. By analyzing the DB Monitor Data, you can locate performance problems for subsequent optimization. Note that Detailed DB Monitor has certain performance overhead. We do not recommend that you continue using tools below the dotted line, because these three tools have not been improved since SQE was released by V5R2 IBM. Visual Explain (VE) is an essential tool for solving all database performance tuning problems. It displays query plans in a graphical manner, in this article, we will focus on the information contained in VE startup and VE, and how to use VE and other tuning tools to tune "problematic" SQL query requests.

Figure 1. DB2 for IBM I provides a variety of performance tuning tools

Visual Explain graphically displays the access plan generated after your query request is optimized by the DB2 for I query engine, it contains the database object information selected by the optimizer, query execution information, and corresponding environment information. You can analyze a series of performance problems such as query request performance bottlenecks and unsatisfactory index policies through VE, or even analyze the results without running query requests.