Capture SQL jobs from different sources using InfoSphereOptimQueryWorkloadTuner

The previous article in this series introduced the concept of the access path, showed you how to read the access path graph in OptimQueryTuner, and introduced in detail how to tune each query.

The previous article in this series introduced the concept of the access path, showed you how to read the access path graph in Optim Query Tuner, and introduced in detail how to tune each Query.

In section 3rd, we will introduce how to optimize SQL workloads. This article will learn how to use InfoSphere®Optim™Query Workload Tuner captures SQL workloads from different sources, collects statistics and index analysis, compares access plans, and executes plan locking and plan management. The goal of this article is to ensure that®DB2®The optimizer obtains the information it needs to make DB2 query decisions based on the best performance. This article also provides some suggestions to help the DB2 optimizer improve access, for example, collect necessary statistics and create the best index.

This section describes how to optimize a single query. With the support of Query optimization tools such as IBM InfoSphere Optim Query Workload Tuner (IOQWT), application developers or administrators can analyze the access paths of a single Query and collect more statistics, rewrite queries or change designs to improve performance.

The purpose of workload performance tuning is to ensure that the application meets the Service Level Agreement and the system's optimal total cost of ownership (TCO ). This article provides a method to use IBM InfoSphere Optim Query Workload Tuner (IOQWT) for Workload tuning.

Workload optimization and Query Optimization

Single query optimization focuses on the performance of specific queries, while workload optimization focuses on the performance of all queries in the workload. Whether performing workload optimization or single query optimization, the goal is the same: improve performance. Compared with single query optimization, workload optimization has many advantages:

1. Improving the performance of all queries can reduce TCO and increase opportunities to meet service level requirements. However, an application may include thousands or even more queries. it is not practical to optimize a single query for each query.
2. It may take a lot of repetitive work to determine which statistics will benefit each query and facilitate subsequent collection of these statistics. As a DBA, a comprehensive RUNSTATS recommendation is helpful to avoid repeated RUNSTATS execution.
3. Query Optimization can identify secondary indexes or change existing indexes. The Analysis and query by means of isolation does not take into account the impact of index changes on other queries, and may lead to too many indexes, which affects data maintenance and management.
4. Identifying and collecting more statistics for a single query may lead to an improvement in one query and imbalance in other queries. It is often inaccurate to use the phrase "cannot be" in customer workloads. Correct an evaluation error. Other query errors that are not analyzed may be exposed.

Workload Optimization Method

Although tuning a single query has the preceding disadvantages, it allows you to focus on improving the performance of the most important query. When analyzing the entire workload, not every query can share the same importance as the business.

You can assign different weights to each query based on the execution count, accumulative time consumption, CPU time, average time consumption, or CPU time. Another method may be to capture some of the most time-consuming SQL statements for optimization and ensure that the sample size is large enough to overcome the limitations associated with single query optimization.

Regardless of the standard used, the general optimization method consists of at least four logical steps:

1. Determine the sample workload to be optimized.
2. Tune the workload.
3. Review recommendations and apply them.
4. Verify and compare the performance before and after optimization.

Step 2-4 forms an optimization cycle that can be iterated. This article describes each part. The next section describes some best practices for capturing and tuning workloads using IOQWT.