Troubleshooting
Problem
The SQL optimizer is predictive and selects from a list of plans based on the estimated execution time. Each input parameter used can influence the estimate of the plan and change plan selection between runs of the query. This document describes the parameters that affect query performance and an introduction to diagnosing query performance issues.
Symptom
IBM DB2 for i contains an advanced cost-based optimizer that uses robust algorithms to quickly implement queries using the least amount of resources possible. In order to do this, the query optimizer creates access plans using information available from the application, infrastructure, and data (including SQL indexes, DDS-Logical Files and SQL Statistics). If information is missing, the optimizer relies on something called default filter factors (default guesses). If information is available but does not accurately reflect the Database Engineer's expected outcome of the query, then the performance of the access plan is at risk.
Each time the IBM i system is asked to optimize a query, the optimizer first has to review what information is available to it at that time. If any of the information changes from run to run, performance can also change. Additionally, if the information is now missing or unsatisfactory, then the access plan may not perform as well as it did in the past.
Several factors can influence consistently good performance; optimal implementation methods are achieved when the factors listed below are reviewed and accurately set to match both the business and technical requirements of the query.
Cause
The following factors influence query performance:
- Availability of Views/Indexes/Temporary Indexes
- Server configuration & attributes such as PTF levels
- Version/Release/Modification level of the server
- Server performance
- Job & query attributes
- Quality of the SQL request
- Cursor sensitivity
- Cursor reuse
- Symmetric Multi-Processing product enabled
- Database design/data model
- Table size/number of rows/number of deleted rows
- Optimization goal
- Fair share of memory
- Existence of background statistics
- Data skew
- SQL environment differences (e.g. STRSQL has special algorithms available to it that are not available in any other SQL environment)
Environment
DB2
Diagnosing The Problem
Visual Explain provides a graphical representation of the optimizer's implementation of a query and can be found in the IBM i Navigator or IBM i Access Client Solutions (ACS) products. The tool relies on data collected in a Database Monitor, Plan Cache Snapshot, or real-time via queries being run in Run SQL Scripts or via Show Statements in the Plan Cache. Consider collecting Plan Cache Snapshots on a regular basis or before changes occur so that these Snapshots can be used for comparison purposes.
Resolving The Problem
Many of the factors listed above are displayed in Visual Explain. It is suggested that the user compare data showing a fast run of the query to data showing a slow run of the same query and are any of the above factors different? If all of the factors are the same, then investigate the accuracy of the above factors. For example, if no indexes exist to support a critical query with a join condition, then the quality of information given to the optimizer is sub-optimal and the optimizer must rely on default values which can cause unpredictable and inconsistent runtimes. If indexes were created, that give the optimizer information about the JOIN condition, now the optimizer can consistently implement the query using the most efficient JOIN order.
See the Related URL section for additional resources available to Database Engineers.
Related Information
Was this topic helpful?
Document Information
Modified date:
26 January 2024
UID
nas8N1021655