Have all your SQL members an “equivalent” elapsed time?
Hey, just a quick newsletter this month to highlight an interesting observation I had recently…
The high-water mark for elapsed time for some SQL was inexplicably high…
At a customer site, the Lead DBA and I were analysing SQL performance—using our DB2 SQL WorkloadExpert tool—and we saw in the data-sharing aggregated view of the executed SQLs that the high-water mark for elapsed time for some SQL was inexplicably high.
We then viewed the data at the Member level for these SQLs and what we saw practically jumped right out of the data at us. The same SQL on one Member was taking up to 45% longer to execute when compared against another member of the Data-sharing Group. Time to see what was going on…
High elapsed times and lots of Wait times on one member
On the member in question there were very high elapsed times and lots of Wait times—all well in excess of the other members. Viewing the SQLs that ran on the “problem” Member, it was quickly apparent that data change SQLs, (Insert, Delete, Update and Merge), were happening orders of magnitude more often than on any other member in the group. This started giving the Lead DBA a good idea about where the Problem could lie.
The problem was Logging
On this member, the OUTBUFF was increased in size, both the Active and Archive Logs were increased in size, and some traffic was switched to another Member in the group.
Tra la! The problem was fixed! All members now have an “equivalent” elapsed time for the same SQL.
When was the last time that you took a look to see how your workload for SQL Updates is “balanced” across your machine? Just saying.
As usual, any comments or questions are welcome!
TTFN
Roy Boxwell