The larger body of tuning knowledge can be inter-exchanged between computer platforms, operating systems and database management systems. The types of tuning opportunities presented for DB2
databases also have corresponding applications with Oracle, Sybase, CA-IDMS and the large repertoire of PC databases. Code examples will often be portable to other environments, with a few
modifications.
The best way to get performance is to pay for tuning up-front. It is not a hidden cost if included in the initial design effort and is exponentially cheaper than performing tuning as an add-on
feature. As an advocate of the “Performance Design Review”, I always recommend that a tuning review is part of both the “Database Design Review” and “Application Design Review” processes.
Now, I would like to share with you some of my personal advice on tuning. These ten principles were discovered through trial and error. I’m certain they will help you avoid heartaches.
Ten Tuning Principles
- Your biggest gain will not result from how well your tuned code performs. Instead, your biggest gain will be derived from how your improvement is viewed by your peers. Ask
yourself these questions for each tuning objective. How can I be proactive with other members of the tuning team? How do I avoid setting management expectations too high? Have I touched all the
bases and obtained all the approvals? What will guarantee accurate test results? How can I share my success with everyone involved? Will my proposed improvement sacrifice existing functionality,
expansion, serviceability or integrity of this application? Have I sought to make this tuning change a win-win scenario for everyone involved. -
Deliverables far outweigh projects in-progress! Be sure to set attainable goals and then stay focused on each deliverable until it is in production. You will find, that as you
uncover layers of code in an application, many new opportunities will unfold. Each one will look like a pot of gold. But, every new task will postpone the delivery of one already in-progress. -
Avoid problems due to semantics. There are so many terms floating around which can be confusing. Clearly describe the ramifications of each tuning change and its benefits using
concise terms and definitions. -
Concentrate on the big gainers. Use the Pareto Principle, 80 percent of the results flow out of 20 percent of the activities. If your first tuning change involves making 100,000
lines of code changes, you’ve missed the point. Simple global tuning changes will yield larger returns, and sooner. -
Get help. Tuning is not a one man show. Sure you can tackle some pretty big things. But, in the end the overall impact of a group of people can be quite astounding. This is the
only way you will be able to defer a hardware acquisition and save your company millions of $$$. If you continually advertise your success to upper management and the I/S. organization as a
whole, you will gain supporters who want to achieve the same goals within their groups. You will gain visibility if you select a catchy name for your tuning group. The “Tuning Mission”,”CPU Swat Team” or “Blitz Team” are good names for the people who have come together. It also helps to setup a status report to management on a weekly or monthly basis. Post this status in a public bulletin board on EMAIL to gain further visibility. Visibility will help you to gain momentum for your tuning project. -
Measure your success accurately. Don’t promise 50% improvement, when in fact you only meant a 50% EXCP reduction. This suggestion will contribute greatly to your credibility and
avoid embarrassing apologies later. -
Put simply, tuning involves three basic activities. The first is eliminating unnecessary processing. The second is eliminating redundant processing. The third activity is using more
efficient processing in exchange for less efficient processing. Stated differently, tuning merely changes the rate and amount of resource consumption, or exchanges an available resource
for one which is exhausted. Keep this in mind while you are browsing line 33000 in a COBOL program for tuning opportunities. Let your brain find the solutions before your eyes become fatigued! -
Shared resources are affected by the principles of queuing theory. You can often identify the bottlenecks for shared resources, as being the code which runs the longest. In an
online system, a correction to a program which ties up resources exclusively will often remove the peg which is holding up the whole table of cards. All the cards drop to the next level, where
some other program now becomes the bottleneck. If you can remove enough of these pegs, the entire systems will collapse down to very efficient processing. I like to think of this as the
house-of-cards or cascading effect. I didn’t invent it. But, I sure have seen it happen. If you are instrumental in causing it to happen somewhere, it will bring you immense joy. -
Robust systems have special tuning needs. You may identify a performance problem in a program which is nested 16 levels deep in an application thread, which is accessed
concurrently. The design complexity alone may be responsible for any inherent performance problems. Recognizing robust systems and their limitations is, a labor intensive, but viable aspect of
tuning. You may not recognize the true cause of poor performance until you have sifted through the many layers of the system architecture. But, have faith. In time you will be able to tame the
beast. - You must always remain cognizant of the fact that programs operate within a fixed arena. A processor has a fixed number of MIPS (millions of instructions/sec), DASD storage (3390s, Mass Storage
Devices, etc) and memory (200 MB real & virtual). Capacity planning is a process which works hand in hand with database, application and system tuning. If an upgrade is inevitable your tuning
efforts were not in vain. The resources you save today will postpone a hardware upgrade and carry forward to future processors.
