We are all seeing it ! The databases are getting bigger and bigger and the queries are becoming more and more complex. Under these circumstances, if a particular SQL query does not perform well then it is very critical to find and remove the performance bottleneck as soon as possible. In this blog, I would like to share a systematic step-by-step method to tune the given query.
Prerequisite: We need to know the most important resources required for query processing.
A query is processed in two phases: compilation and execution. During compilation phase, the most important work done is query optimization. The optimizer considers various access plans and chooses the least expensive one for executing the given query. During execution phase, the query is actually executed by following the steps mentioned in the access plan.
For query-tuning purposes, it is very important to know the major resources required for the query compilation and execution phases. The compilation phase will use/access statement-heap, catalog-cache, package-cache, etc. The execution phase will use/access package-cache, sort-heap, shared-sort-heap, lock-list, buffer-pool, etc. The following block-diagram shows these resources. It also shows which resources are part of agent's private memory and which resources are part of database global memory.
Step 1: Monitor the actual resources consumed while DB2 was processing the given query.
By using access plan ( db2exfmt output ), DB2 Snapshots and/or DB2 SQL Monitoring Functions, we can quickly identify the heavily utilized resources for processing our SQL query. Sometimes, it is enough to know which resource was not sufficient for our query. Let us consider a simple example.
Example 1: If the db2exfmt output shows the following message, then it tells us that statement heap was not enough for query compiler:
SQLCA : (Warning SQLCA from compile)
SQLCODE 437; Function SQLNO***; Message token '1'; Warning 'None'
In this case, the access plan could be sub-optimal. Increasing statement heap should help here.
However, many times, it is not enough to just know the heavily utilized resource. We also need to find out the context in which, the resource was utilized. This is where access plan analysis helps a lot !
Step 2: Analyze the access plan to get more insights.
The access plan ( generated by using db2exfmt tool ) provides good insights for query-tuning. We can find out the “context” in which , the given resource was heavily consumed. Let us take an example:
Example 2: If the snapshots/monitoring-functions show one or more of the following symptoms, then it means the sort-heap was very heavily consumed:
- sort overflows
- rows written for a SELECT query
- temporary data logical/physical reads
- hash loops
- hash join overflows
In this case, we need to use section-actuals and find out if the input cardinality of SORT/HSJOIN operation was underestimated or not. If underestimated, then we need to provide more statistics to help optimizer choose a better access plan. Otherwise, we can create an index, which may help us avoid the SORT/HSJOIN operations altogether.
In this way, we can find out the reason behind slow running query and then figure out ways to improve the query performance. In this blog, I have given a high level overview of the step-by-step query tuning method. Stay tuned for some concrete examples ! In my next blog, I will share a scenario, where a statistical view helped improve query performance.
Disclaimer: The postings on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.