The most popular way, but not necessarily the best way, to analyze statements is to hunt elephants. Far too often we hear IT professionals (a broad group including DBAs, DBA Managers, and IT Directors) screaming "WHAT'S HAPPENING RIGHT NOW?!?!?!?" and the elephant hunt begins. By elephant hunt, I mean the vigorous search and destroy mission to find that rogue "one off" (we hope) statement that is sucking the life out of the system and damaging the business. Once the elephant is identified, it is often promptly terminated (db2 "force application (APPL-HANDLE)" ), and then the search for the guilty party begins. The submitter of the elephant is contacted (if they can be identified) and educated on query best practices and organization standards.
Databases with several elephants tend to be I/O bound. Data warehouse databases are particularly prone to elephant stampedes and are usually I/O bound.
I concede that the occasional elephant hunt is a necessary task that most DBAs must periodically endure, but quite frankly, I largely find elephant hunting to be an exercise in futility.
If there was a forest fire and your home was in eminent danger, it would obviously be prudent and necessary to extinguish the trees on fire in the immediate vicinity. Wise fire fighting organizations, however, also appreciate the importance and value of a helicopter view from 10,000-15,000 feet. Wind directions and speed must be considered, along with precipitation, rivers, lakes, and structural surface improvements. After obtaining the 'bigger picture', forest fighters can clear a path ahead of the fire to extinguish it.
When you go on elephant hunts, you are chopping down, or otherwise extinguishing, individual trees that are on fire. Without the bigger picture, your forest fire may continue to burn despite the individual successes of extinguished trees.
In case it isn't clear, I am trying to inspire you to think about forest and wildlife management within your organization's databases. In Australia, the forest management service regularly, periodically, deliberately sets forests on fire under carefully controlled circumstances. This seems counter intuitive, if not crazy, but by deliberately managing forest burns the fire fighters are able to avoid catastrophic fires that rage out of control and the burned areas rapidly rejuvenate as a healthier forest.
I want you to be in control of your databases and their performance. I want you to be able to regularly meet and exceed your organization's service level expectations. I want you to get gold stars for your foreheads and I hope to help you become a Performance Hero in your organization. To achieve these goals, I need you to understand the importance of statement workload analysis and management.
When I teach classes or hold conference seminars, I usually ask participants the question "Who here has been bit by a mosquito?" Most participant hands usually go up unless they've fallen asleep or not gone outdoors all of their life. The next request I make of participants is "Now please keep your hands up if you have ever died from a mosquito bite." As you'd expect, all hands go down.
Mosquito bites itch. They are a nuisance, but they certainly aren't as dangerous as an elephant charging at you. However, if a person were to become covered by mosquitoes, their outlook isn't very good either. Swarms of mosquitoes can be just as fatal as an angry elephant. In your databases, small, relatively inexpensive statements that are executed with high frequency can be devastating to your organization's business performance.
Let's talk about aggregated costs for a moment. I, like most of you, have a cell phone (or mobile phone depending on your country of residence and preferred terminology). I get a bill each month. I hate my cell phone bill. It details every incoming and outgoing call in painful detail including the time of the call and the number of minutes used. At the bottom of my bill, I often discover I've used more minutes than my cell phone plan allows so I get hit with costly surcharges for minute overages. I would like to understand where all my minutes go. If I could determine which phone numbers were costing me the most minutes, I could perhaps manage my phone time better and avoid the costly overage charges. The wireless cell phone carriers don't want me to do this - those extra overage minutes provide them with a lot of profit revenue. It sure would be nice if my phone bill included a roll-up report that showed the output from a query like "SELECT PHONENUM, SUM(MINUTES) FROM PHONEDETAILS GROUP BY PHONENUM, ORDER BY 2 DESC". If I had this phone cost information, I could easily ascertain the source of my highest phone costs --- and I would probably speak with my spouse less often!
When we perform database statement workload analysis, we need to similarly aggregate statement costs over periods of time to obtain 'the big picture' so we can effectively manage our database forests, efficiently extinguish fires, and proactively prevent fires. When we aggregate database statement costs over time, we can identify the sources of resource bottlenecks and problems, isolate costly statements, and readily develop cures for statements that are damaging to business performance. Once you fully understand and master statement workload analysis with cost aggregation, you will become a true, invaluable, indispensible, Performance Hero in your organization.
This blog post has exceeded a reasonable length, so this concludes our introduction to statement analysis methods. I hope you are beginning to fully appreciate the importance of statement workload analysis and cost aggregation. In upcoming blog posts, we will go into greater depth on techniques for hunting elephants and repelling and killing mosquitoes. In the meantime, please don't play with matches, be careful with your camp fires, and don't throw cigarette butts out of your car windows.
The Shameless Marketing Moment
If you are attending IDUG Europe in Warsaw Poland, please consider attending the Education Seminar "DB2 LUW Performance Diagnosis Learning Lab". At IDUG North America, this seminar had the second highest attendance of all Education Seminars and it received an overall score of 3.94 out of 4.0. You will learn a lot and have fun too.
You can also find DBI at additional conferences this fall.
Just for Fun
If the high price of fuel is taking too much cash out of your wallet, check out these Top 10 Tips for Improving Your Fuel Economy provided by Edmunds. Tip #2: You can reduce fuel consumption by 3.3% by simply maintaining proper tire pressure.
If you want to achieve GREEN IT initiatives and lower data center power consumption, please CLICK HERE.
Cheers,
Scott
Scott Hayes
President & CEO, DBI
IBM DB2 GOLD Consultant