Pool Advisor for DB2 was the first of many BMC Software advisory products that combines the intelligence to advise with the ability to act. Available since October 2000, Pool Advisor for DB2 provides a variety of DB2 pool tuning options that address the health of all DB2 pools (buffer, EDM, RID and sort pools) within the context of the total virtual storage environment and user supplied controls. It can also manage the efficiency of the global dynamic statement PREPARE caching, potentially providing large savings in heavy dynamic SQL environments. With the addition of the Configuration Advisor component, this product also will predict the optimization of its proposed buffer pool layout.
With the “z” hardware, zOS, and DB2 releases since DB2 v8, there has been a steady march towards support and exploitation of ever larger amounts of virtual and real memory. Because of this there is a commonly expressed opinion that memory -related tuning is no longer necessary, that there is so much memory now that one doesn’t really need to worry about tuning its use. Like most old wives’ tales, there is a kernel of truth there that tempts many to believe it. But also like the tales of old, things are never that simple and believing that it is that simple will get you into trouble more often than not. One thing is certainly true in most cases. Data growth is even faster than the hardware and software growth in storage capacity. Gigabytes of memory may be common, but Terabytes of data are even more common. And all the evidence indicates this relationship will not be changing. If there is any rule of thumb that is more likely to keep you out of trouble it is this: there is never enough memory.
Pool Advisor for DB2 works by capturing and performing periodic analysis on all the standard performance metrics, including GETPAGE activity (without tracing!), hit ratios and I/O rates. These statistics are retained internally for a 48-hour rolling interval, providing quick and effective short-term performance tuning of workloads. With the unpredictability in today’s applications due to ERPs, e-business and global demands, it is critical to be able to adjust storage resources dynamically. Since Pool Advisor constantly monitors and analyzes DB2 resources, performance problems can be corrected before they become critical. Pool sizes can be increased and decreased and thresholds adjusted for best overall storage performance (taking MVS paging rates and pool access page faulting into consideration). Online assistance provides detailed and summary explanations of the analysis, benefiting novice and advanced users. Pool Advisor takes the difficult work out of understanding extensive performance statistics.
Besides virtual storage management, the primary tuning methodology that Pool Advisor uses is based on standard industry techniques advocated by IBM and tuning professionals for years. This methodology involves
- classifying the access characteristics of all your database objects
- grouping them into pools so that objects with similar characteristics are together (and separated from objects with very different access characteristics), and
- configuring those pools to optimize their performance for the kinds of access techniques their assigned objects use the most.
- Traditionally, a dedicated tuning professional or consultant has been required to initiate performance traces, perform complex data analysis and implement configuration changes in order to tune DB2 subsystems. Today, there is usually only an overworked administrator or DBA attempting to manage many DB2 subsystems with many applications and literally many thousands of objects. As a result, tuning is usually performed at only the most basic level. If detailed analysis is undertaken, even to the point of simulating the effect of different configurations, the effort is substantial and the result is far from foolproof. Changing workloads and global MVS effects, such as paging, cannot be predicted, and pools are periodically over or under utilized anyway. Not to mention all the other DB2 subsystems for which there was no time or budget for the same level of analysis. Clearly, automation is desperately needed. One administrator can manage many DB2 subsystems¾and do it well¾if there is a tool to automatically collect and analyze data, suggest changes, perform those changes automatically when appropriate, and call for help when a problem arises (before users begin to notice application response problems and pick up the telephone).
Since Pool Advisor for DB2 takes a pro-active approach to tuning, it provides an optional alternative when needed. You have complete control and choice over the actions taken and over the ranges the various pools are allowed to vary. You can use Pool Advisor initially as a super DB2 storage usage monitor. As you build confidence in the actions that it recommends, you can choose to enable specific automations. This allows you to get to the point where those overnight performance problems can be handled without you!
Pool Advisor for DB2 provides the ability to analyze object access patterns and calculate optimal buffer pool configurations for improved application response times and overall throughput. You have the option to control the complexity of the buffer pool configuration, including specification of the maximum number of pools and the identification of critical objects. There is also the capability to perform “what-if” scenarios, resulting in predictions of the effectiveness of the resulting configuration. Unlike other tools in the market place, our configuration analysis will not only recommend better configurations, it is capable of creating DDL for scheduled execution without leaving the product. It provides long term history correlated with change activity, providing valuable application trending and resource usage analysis.
There are two significant advantages with our approach. First, the collection agent can be left on, giving a longer and more accurate picture of system activity. It is important to realize that traditional tuning based on short data collection "snapshots" results in configuration decisions that address only the conditions existing at the time of the snapshot. If your tuning is based on longer collection samples, the peaks and valleys of resource use will be averaged, and that can result in performance problems during times of peak demand. Real-time analysis is the only way to detect and handle performance issues before they become serious performance problems. Second, Pool Advisor lets you easily access analysis and recommendations, and take corrective action directly, without having to arduously review many separate reports. Manual effort is dramatically reduced and you don't have to spend all your time becoming an expert on esoteric DB2 internals! With our integrated solution, adjustments to DB2 can be made immediately, without ever leaving Pool Advisor for DB2.
If you have BMC Software’s APPTUNE for DB2 or Mainview for DB2, data collection resources are shared with minimal additional overhead. Data Collection has been made zIIP eligible, reducing the cost and overhead of data collection offloading 50% to 80% of Data Collector overhead to zIIP engines. The data collector started task requires only one address space per LPAR, no matter how many subsystems you have. Pool Advisor can be started and stopped at will, but it is important to monitor DB2 at all times so that it can detect problems, issue alerts, and/or take corrective actions. Pool Advisor has a sophisticated method for collecting data that minimizes overhead to DB2 applications dramatically compared to techniques depending on DB2 performance traces. In addition, Pool Advisor has integrated anti-thrashing logic and a periodic analysis/change cycle specifically designed to react to true adverse performance trends without over-reacting to transient workload spikes.
In summary, Pool Advisor for DB2 provides unique data and functionality that cannot be found in any monitoring tool, and analysis and recommendations that cannot be found without significant effort in any other tuning tool. Pool Advisor uses “turnkey” technology for DB2 and has the ability to work with other BMC products to detect problems and make corrections before they cause a crisis.