Once root cause problems, or opportunities for improvement, are accurately identified, next you hopefully want solutions that will mitigate the problems!
Many DBAs will try to interpret Explain plans to arrive at a solution which DBI can help you easily do. But we assume you are in a hurry and we've only
got five mouse clicks or fewer to work with, so we'll cut to the chase in our next short video clip illustration...
What are the best, most valuable solutions?
Not all solutions are created equal. Presuming you've used the IBM Design Advisor (db2advis) before, you've probably noticed that sometimes multiple
indexes are recommended as part of the solution set. You probably also intuitively know that some of the recommended indexes are more valuable or
important than others. Yet, if you look at the XML output, the stated BENEFIT of each index will likely be represented as the same for all of the indexes.
If the BENEFIT values are different, they're usually inaccurate. This leads us to another DBI innovation --- Advanced Index Benefit Analysis which
actually determines the accurate value of each recommended index through processes of Index Addition and Subtraction.
Index Addition answers the question "How much value, or what percentage of savings, is obtained if this index, and only this index, is created?" Index
Subtraction presumes that some indexes might be working together in a team effort, so Index Subtraction answers the question "How much value is lost by
creating all of the recommended indexes except this one?" Index Addition and Subtraction tell you instantly which indexes are the most important to the
solution set while helping you avoid the creation of those that offer minimal value --- no more guessing! Scott Hayes presented about this Advanced Index
Benefit Analysis at IDUG in 2015 during tech sessions "Sage Advice Part 2: Advanced Index Benefit Analysis." If you missed this presentation and would
like to learn more, watch a replay of The DB2Night Show Episode #168.
Do you have your bufferpools sized correctly according to activity? Often times we see bufferpools of various sizes, yet the smallest bufferpools are
incurring the heaviest logical read activity while large pools aren't being utilized! Wrong! Size should correlate to activity!