After a short absence, I am now back with my newsletters on a regular basis. During the summer, when you were (hopefully) relaxing and enjoying time with friends and family, my co-workers were busy here updating our websites. You may not see a big difference in “the look”, as most of the improvements were behind the scenes, but we have now migrated to a completely new platform and intend to be adding more features and enhancements to our sites as we move forward; all in the name of providing even better service for our customers!
As many of you know, August is the time of year us Europeans take our long summer vacations. Dreaming of Ice-cream in Italy, the balmy breezes of the Balearic Islands, or – in my case – the dense fog banks of Dover, (what white cliffs?).
So I’ll keep this newsletter short, while I unpack my swimsuit. For the past several months, I’ve been visiting various customer sites on DB2 roadshows. It’s always fascinating to re-acquaint myself with some real-life situations, but it never ceases to amaze me that so many sites still rely on the same traditional methods that have been used for the past 10 – 20 years.
Poorly designed SQL
Modern structures, languages, APIs etc. do nothing to stop bad, poorly-designed, SQL from landing in Production. It is, at the end of the day, the DBA who is left to pick up the pieces and try to figure out how to get a quart *out* of a pint pot, (or a large frame into a small swimsuit.) One of our newest product developments is called SQL WorkloadExpert (WLX), and it was designed to work for DB2 10 NFM and above only. Why, you ask? Because it needs the new and enhanced IFCIDs that came with that release – notably the enhanced 316, which gets written when a Dynamic Statement Cache is flushed, and the new IFCIDs 400 and 401, which give the same data but from the EDM Pool for static SQL. (Personally, I refer to this as the Static Statement Cache, or SSC.)
Build a Data Warehouse containing all the Dynamic SQL
This new and useful data is used to build a Data Warehouse containing all the SQL that is running in a plex. Also, using EXPLAIN data, DB2 Catalog data, and the RealTimeStatistics table, it builds up a complete picture of who is running what, when – and how often – on your machine. This is particularly interesting for some of those sites that I visited recently. It is truly amazing how much “low hanging fruit” is out there for really, really simple tuning that yields a huge bang for the buck! The aforementioned IFCIDs establish performance metrics that give you a handle on where CPU, IO, and Elapsed time is all going. These metrics cause the so-called “bad guys” to virtually leap off the page! The most quantitative metrics that are most useful for the “first” tuning aims, are the Executions per hour, CPU per hour and IO (Getpages) per hour.
2,500 seconds of CPU per hour reduced only by adding one little index…
For example, at one of those shops I saw the following: (This is a very large shop with 18 way data sharing and 10’s of CPUs per machine) One SQL was running 1.3 Million times per hour, using 2,500 seconds of CPU per hour and 240 Million getpages per hour. This is intense! The SQL was not particularly complex in this case either, and the per execution rates were not particularly significant. However, by simply adding one little index, the site reduced CPU consumption to 1,700 seconds per hour and 180 Million getpages per hour! Immediately, this provided a savings of 40% overall by providing such a huge performance boost. Another SQL’s CPU consumption was reduced from 17,111 seconds per hour down to just 16 seconds per hour! …Wow. Finally, a third SQL had an Elapsed time of 30,000 seconds per hour reduced down to 30 seconds per hour! The point I am trying to make is: Don’t always trust blindly in your traditional Top Ten reports. By digging just a little deeper, you may be able to find some very easy (tuning) fruit, ripe for plucking. And now I’m off for my well-deserved rest. Now how do I tune my mind back in to “work” mode…
Happy Tuning!
As usual any questions or comments are welcome,
TTFN Roy Boxwell
Senior Software Architect