The SQL Performance Solution for DB2 includes a set of features collectively called the Performance Advisor. These features include Reorg Advisor, Workload Access Path Compare, Recommindex, and Exception Advisor.
Exception Advisor assists in diagnosing the root cause of performance-related problems. In this article, we’ll explore its wide range of capabilities and provide a general overview of how to set it up.
Exception Advisor examines the data accompanying the triggered exception and compares that data to past execution statistics for the same statement in baseline or aggregated tables. To use Exception Advisor, the user must set up exception definitions in the APPTUNE administration panels. The data collector will capture performance data including exception records. Next, you will need to load the performance data into the Performance Advisor Database (execute the supplied load jobs) and run the Exception Advisor job. For more information on setting up and managing the Performance Advisor Database, see Chapter 3, Managing performance with Performance Advisor (BMC support login required).
Exception Advisor compares exception records for elapsed or CPU time to historical performance records for the same SQL statement to produce analysis and advice based on a rules dataset. Since it has already captured this information, there is no additional overhead required to capture the performance data. The rules dataset can be customized to adapt the recommendations to your organization’s preferences.
Here is an example of an entry from the rules dataset:
RULE:Timeouts per Escalation;
RATIO=QTXADEA+QTXATIM/QTXALEX+QTXALES; > 0
This statement experienced a timeout or deadlock, most likely due to
RULE:SyncIO Wait percentage;PERCENTAGE=SYNCWAIT/ELAPTIME; > 30
This execution spent a large percentage of time peforming
synchronous I/Os. If the getpage count has risen, it can indicate
a need to rebind the program, update catalog statistics for accessed
objects, or reorganize affected objects. Also check for RID list
failures for the statement and buffer pool performance for accessed objects.
In diagnosing performance exceptions, it can be helpful to know what issues are NOT a cause of the problem so that you avoid wasting time in your diagnosis. That point is illustrated by this report (click to enlarge image):
We can see that there is no correlation between CPU time and LOCK WAIT PERCENTAGE. The user does not need to spend time looking into locking problems for this statement. Let’s look at another report to see if a correlation exists between the exception and other statistics (click to enlarge image):
In this case, we can see that historically, there has been a high correlation between the CPU exception and both SYNCIO wait percentage and GETPAGE count. Consequently, this would be a more likely cause of your high CPU exception.
Another advantage of seeing the history of the exception is to determine if the problem arose gradually or was a sudden spike. This would help you correlate a recent system or database change to the performance problem.
Thanks to Ann Darks, Mike Behne and Mike Watkins from BMC Software for their contributions to this article.