Most likely many of my colleague DBAs have been summoned into the IT war room after a release moment. The last invitation I received was because a transaction (jdbc source) consumed CPU to the extent that the entire production was impacted. Another frequent reason to be invited is that the performance of a query is so slow that users complain the system is unusable.
Identifying the issue
In those cases, it is easy to find the culprit using a performance monitor but solving it instantaneously is often not as easy.
Once you capture the query text you can start analyzing and the dba reflex is using explain. When parameter markers are involved, I ask the developer to give me the values that would be used at runtime. And my second question is why they didn’t test the performance of the query. Standard answer is that they did, but they tested the query interactively with a query tool using literals instead of running the program with the parameter markers.
I have an example query that will show a different access path when one uses literals instead of parameter markers.
Query text with literals instead of parameter markers:
Select T1.CONTR_NBR, T2.EVENT_NBR, T3.EXCHNG_DT, T3.ENTITY, T1.CONTR_CAN, T1.EXPIR_DT, T2.EVENT_DT
from CONTRACTS T1, EVENTS T2, ADMIN T3
WHERE T1.CONTR_NBR > '2004-03-09-16.51.43.843068'
AND T1.CONTR_NBR = T2.CONTR_NBR
AND T1.CONTR_NBR = T3.CONTR_NBR
AND T2.CHARSTATE IN ('R', 'K', 'V')
AND T1.STATEPRO = ' '
AND T2.EVENT_NBR = (SELECT MAX(EVENT_NBR) FROM EVENTS WHERE CONTR_NBR = T2. CONTR_NBR AND CHARSTATE NOT IN ('G', 'L') )
AND T3.ADMIN_CR_DT = (SELECT MAX(ADMIN_CR_DT) FROM ADMIN WHERE CONTR_NBR = T3. CONTR_NBR)
ORDER BY CONTR_NBR ASC limit 50 WITH UR
;
When running a db2expln for the above we learn that the access plan starts with a table scan of CONTRACTS and ADMIN. Data for the table EVENTS is retrieved twice via index only access and there is an index access for the subselect on the ADMIN table, also the Estimated Cost = 15244037,000000
What follows is the first part of the explain, which shows the table scans:
Estimated Cost = 15244037,000000
Estimated Cardinality = 0,000000
Process Using 2 Subagents
| Access Table Name = SSLC.CONTRACTS ID = 583,4
| | #Columns = 4
| | Parallel Scan
| | May participate in Scan Sharing structures
| | Scan may start anywhere and wrap, for completion
| | Fast scan, for purposes of scan sharing management
| | Scan can be throttled in scan sharing management
| | Relation Scan
| | | Prefetch: Eligible
| | Isolation Level: Uncommitted Read
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| | Sargable Predicate(s)
| | | #Predicates = 2
| | | Process Build Table for Hash Join
| Hash Join
| | Early Out: Single Match Per Outer Row
| | Estimated Build Size: 27840000
| | Estimated Probe Size: 321072000
| | Bit Filter Size: 563712
| | Access Table Name = SSLC.ADMIN ID = 602,4
| | | #Columns = 4
| | | Parallel Scan
| | | May participate in Scan Sharing structures
| | | Scan may start anywhere and wrap, for completion
| | | Fast scan, for purposes of scan sharing management
| | | Scan can be throttled in scan sharing management
| | | Relation Scan
| | | | Prefetch: Eligible
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| | | Sargable Predicate(s)
| | | | #Predicates = 1
| | | | Process Probe Table for Hash Join
| Insert Into Sorted Temp Table ID = t1
| | #Columns = 8
| | #Sort Key Columns = 1
| | | Key 1: CONTR_NBR (Ascending)
| | Sortheap Allocation Parameters:
| | | #Rows = 2662214,000000
| | | Row Width = 52
| | Piped
| | Compressed
| Access Temp Table ID = t1
| | #Columns = 8
| | Relation Scan
| | | Prefetch: Eligible
When we modify the query and change the predicate T1.CONTR_NBR > '2004-03-09-16.51.43.843068' into T1.CONTR_NBR > ? and run explain again, we get a different access path. First, the Estimated Cost decreases from 15244037,000000 to 1683,731567 which looks promising. There are no table scans left, CONTRACTS and ADMIN are now processed via an index access. Also note that Db2 is starting with the index on table EVENTS and index access on CONTRACTS instead of table scans on ADMIN and CONTRACTS.
Estimated Cost = 1683,731567
Estimated Cardinality = 0,000000
Process Using 2 Subagents
| Access Table Name = SSLC.EVENTS ID = 585,4
| | Index Scan: Name = SSLC.EVENTS_IN1 ID = 9
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: CONTR_NBR (Ascending)
| | | | 2: EVENT_NBR (Descending)
| | | | 3: EVENT_DT (Descending)
| | | | 4: CHARSTATE (Ascending)
| | | | 5: ADMIN_TRN (Ascending)
| | #Columns = 3
| | Parallel Scan
| | #Key Columns = 1
| | | Start Key: Exclusive Value
| | | | 1: ?
| | | Stop Key: End of Index
| | Index-Only Access
| | Index Prefetch: Sequential(311), Readahead
| | Isolation Level: Uncommitted Read
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| | Sargable Index Predicate(s)
| | | #Predicates = 1
| Nested Loop Join
| | Access Table Name = SSLC.CONTRACTS ID = 583,4
| | | Index Scan: Name = SSLC.IX_CONTRACTS_01 ID = 1
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: CONTR_NBR (Ascending)
| | | | | 2: STATEPRO (Ascending)
| | | | | 3: FILE_NBR (Ascending)
| | | #Columns = 4
| | | Single Record
| | | Fully Qualified Unique Key
| | | #Key Columns = 2
| | | | Start Key: Inclusive Value
| | | | | 1: ?
| | | | | 2: ' '
| | | | Stop Key: Inclusive Value
| | | | | 1: ?
| | | | | 2: ' '
| | | Data Prefetch: None
| | | Index Prefetch: Sequential(1), Readahead
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| | | Sargable Index Predicate(s)
| | | | #Predicates = 1
| Insert Into Sorted Shared Temp Table ID = t1
| | #Columns = 7
| | #Sort Key Columns = 2
| | | Key 1: CONTR_NBR (Ascending)
| | | Key 2: EVENT_NBR (Descending)
If you cannot measure it, you cannot improve it
But wait, isn’t this contradicting since the IT war room claims our jdbc program with parameter markers is slower and we need to find the why and a solution? We can start looking at the statistics gathered by runstats and calculate the cost of the different access paths ourselves to better understand. Or we run the queries manually and look at the performance metrics collected in the package cache. And here is the tip on how to execute the statement performing with an almost identical access path as if parameter markers are used.
First the metrics when we use literals in the query:
NUM_EXECUTIONS PREP_TIME COORD_STMT_EXEC_TIME TOTAL_ACT_TIME TOTAL_CPU_TIME POOL_READ_TIME POOL_WRITE_TIME
---------------- --------- -------------------- -------------- -------------- -------------- ---------------
1 18 26647 79933 1455643 1118 0
The TOTAL_CPU_TIME is expressed in microseconds, other timings in milliseconds.
Following are the metrics with the query behaving as if it uses parameter markers:
NUM_EXECUTIONS PREP_TIME COORD_STMT_EXEC_TIME TOTAL_ACT_TIME TOTAL_CPU_TIME POOL_READ_TIME POOL_WRITE_TIME
---------------- --------- -------------------- -------------- -------------- -------------- ---------------
1 23 63017 189046 51461279 50642 0
The total response time goes from 26.6 seconds to 63 seconds and the cpu consumption from 14.5 seconds to 51.5 seconds! It seems we must agree with the people in the war room.
How did I rewrite the query in order to run it and performing like if parameter markers are used ? I use a common table expression, the content I define in it has no statistics and so Db2 must use assumptions the same way it does for parameter markers. (The parts that changed are in uppercase).
WITH PARAM (CONTR_NBR) AS (VALUES('2004-03-09-16.51.43.843068'))
select t1.contr_nbr, t2.event_nbr, t3.exchng_dt, t3.entity, t1.contr_can, t1.expir_dt, t2.event_dt
from contracts t1, events t2, admin t3
where t1.contr_nbr > (SELECT CAST(CONTR_NBR AS TIMESTAMP) FROM PARAM)
and t1.contr_nbr = t2.contr_nbr
and t1.contr_nbr = t3.contr_nbr
and t2.charstate in ('r', 'k', 'v')
and t1.statepro = ' '
and t2.event_nbr = (select max(event_nbr) from events where contr_nbr = t2. contr_nbr and charstate not in ('g', 'l') )
and t3.admin_cr_dt = (select max(admin_cr_dt) from admin where contr_nbr = t3. contr_nbr)
order by contr_nbr asc limit 50 with ur ;
And here is the explain for that statement:
Estimated Cost = 114863,093750
Estimated Cardinality = 0,000000
Process Using 2 Subagents
| Access Table Name = SSLC.EVENTS ID = 585,4
| | Index Scan: Name = SSLC.EVENTS_IN1 ID = 9
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: CONTRAT_NBR (Ascending)
| | | | 2: EVENT_NBR (Descending)
| | | | 3: EVENT_DT (Descending)
| | | | 4: CHARSTATE (Ascending)
| | | | 5: ADMIN_TRN (Ascending)
| | #Columns = 3
| | Parallel Scan
| | #Key Columns = 1
| | | Start Key: Exclusive Value
| | | | 1: ?
| | | Stop Key: End of Index
| | Index-Only Access
| | Index Prefetch: Sequential(311), Readahead
| | Isolation Level: Uncommitted Read
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| | Sargable Index Predicate(s)
| | | #Predicates = 1
| Hash Join
| | Early Out: Single Match Per Inner Row
| | Estimated Build Size: 784000
| | Estimated Probe Size: 16864000
| | Bit Filter Size: 25600
| | Access Table Name = SSLC.CONTRACTS ID = 583,4
| | | #Columns = 4
| | | Parallel Scan
| | | May participate in Scan Sharing structures
| | | Scan may start anywhere and wrap, for completion
| | | Fast scan, for purposes of scan sharing management
| | | Scan can be throttled in scan sharing management
| | | Relation Scan
| | | | Prefetch: Eligible
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| | | Sargable Predicate(s)
| | | | #Predicates = 1
| | | | Process Probe Table for Hash Join
| Insert Into Sorted Temp Table ID = t1
| | #Columns = 7
| | #Sort Key Columns = 1
| | | Key 1: CONTRAT_NBR (Ascending)
The access path is not identical to the one where we used the parameter marker, but it comes close. I have used this technique for several cases, I almost never have the same access path when just doing an explain with the parameter markers compared to using the common table expression, but every time the response time was close compared to using literals.
Note: when using the common table expression and the column is a timestamp it is important to cast the column of the CTE into a timestamp otherwise it might influence the access path. In my experience it isn't as important to cast if the column is a varchar or integer.
Solving the issue
What did I do to improve the performance of this query? I did rewrite it as below and then at runtime you supply a value for the parameter marker:
with param (CONTR_NBR) as (values(?)),
nt_contracts(CONTR_NBR, CONTR_CAN, EXPIR_DT)
as (select CONTR_NBR, CONTR_CAN, EXPIR_DT
from CONTRACTS
where CONTR_NBR > (select cast(CONTR_NBR as timestamp) from param limit 1)
and STATEPRO = ' '
fetch first 2000 rows only)
Select T1.CONTR_NBR, T2.EVENT_NBR, T3.EXCHNG_DT, T3.ENTITY, T1.CONTR_CAN, T1.EXPIR_DT, T2.EVENT_DT
from nt_contracts T1, EVENTS T2, ADMIN T3
WHERE T1.CONTR_NBR = T2.CONTR_NBR
AND T1.CONTR_NBR = T3.CONTR_NBR
AND T2.CHARSTATE IN ('R', 'K', 'V')
AND T2.EVENT_NBR = (SELECT MAX(EVENT_NBR) FROM EVENTS WHERE CONTR_NBR = T2. CONTR_NBR AND CHARSTATE NOT IN ('G', 'L') )
AND T3.ADMIN_CR_DT = (SELECT MAX(ADMIN_CR_DT) FROM ADMIN WHERE CONTR_NBR = T3. CONTR_NBR)
ORDER BY T1.CONTR_NBR ASC limit 50 WITH UR
;
How did the above help? I avoid doing a join for all rows that possibly qualify the predicates of the contracts table. The limit 50 at the bottom only gets applied after calculating the entire result set so I try to limit the number of rows to be calculated as early as possible and the response time is a lot better. We go, from the best case when using literals in the original query from 26.5 seconds to 86 milliseconds and cpu consumption from 1.5 seconds to 89 milliseconds:
NUM_EXECUTIONS PREP_TIME COORD_STMT_EXEC_TIME TOTAL_ACT_TIME TOTAL_CPU_TIME POOL_READ_TIME POOL_WRITE_TIME
---------------- --------- -------------------- -------------- -------------- -------------- ---------------
1 15 86 91 89837 0 0
Conclusion:
I use common table expressions regularly in order to rewrite queries that have bad performance. By using the above technique, you can also order the parameters in the same sequence as the original source and then move them around in the rewritten query without having to modify the program that invokes the query.
These days not everyone is as convinced to use parameter markers but here, after rewrite, you would still gain 15 milliseconds for every execution after the first and we have shown that the performance is better than the original query with literals.
#Db2