SQL Tuning involves SQL review as well as a knowledge of potential access paths, table and index data distribution, and statistics.
Resource Constraint Tuning is an analysis of possible tradeoffs among CPU, elapsed time, I/O, memory, network traffic, and other resources.
Application Tuning focuses attention on suites of programs that access particular tables, batch windows, and online service levels.
Object Tuning concentrates on the general definitions and configurations of tables, indexes, stored procedures, and other database objects.
System Tuning deals with the DB2 database management system infrastructure that supports all of the above.
Tools for Tuning
SMF Reporting. There are many tools that input SMF records and produce DB2 reports. These include the DB2 Performance Monitor / Performance Expert from IBM (now the “Omegamon XE for DB2 Performance Expert”) as well as others from third-party vendors.
RMF Reporting. These are reports on resource usage based on data gathered and reported by the Resource Measurement Facility. More for the advanced performance tuner, as they assume an expert level of knowledge of z/OS features. I recommend you check the manuals (“RMF Performance Management Guide” SC33-7992-04, and “RMF Report Analysis” SC33-7991-09) and read up on these reports to begin with:
Coupling Facility Activity
The Three Most Important Performance Reports
Often the DBA must react to production performance problems. The DBA should have a good working knowledge of the following:
In addition, there are three more categories of reports that are most important.
Three More Important Performance Reports
The above three reports are most often used to diagnose short-term specific performance issues. In contrast, the next three reports are used more proactively. The DBA will look at performance of multiple applications or an entire subsystem, estimate resource usage in order to do capacity planning, and attempt to forecast future needs.
Let's cover these three reports in more detail.
Historical Access Paths
The Explain facility produces a report of SQL access path information in the form of rows entered into a Plan Table, a Statement Table, and other performance-related tables. These rows can be stored and maintained in a separate archive and accumulated over time. This provides the DBA with a history of DB2 access path choices for applications and SQL statements.
The DBA can then review what changes, if any, have occurred over time. This may be important for review after table or index changes, major application alterations, SQL enhancements, DB2 subsystem configuration changes, or software upgrades.
In addition to the Plan Table data, the DBA can also archive table, column, index, and statistical data, as well as key and integrity information.
This can be done by designating a “master” Plan Table in which you store all access paths, although you will need to somehow correlate access paths that change due to SQL statement number changes. (One way to do this is by implementing the QueryNo parameter on all SQL statements.) With a master Plan Table created you can now execute various queries of your own. For example:
The Accounting Long Report
This report allows the DBA to drill down into plan/package details. It gives extremely detailed information about Plans and Packages. It is typically used after the DBA has referenced the Accounting Short Report and determined that there may be a performance issue.
For a quick overview of this report, see Figure 1. This diagram gives an idea of how the report appears. Some of the more commonly-referenced sections are noted in color.
Figure 1 -- The Accounting Long Report
Top "n" Reports
By regularly executing and archiving Top "n" Reports the DBA can get an early warning of many problem types. For example, you may run a weekly report listing the top twenty SQL statements with high CPU usage. If one statement remains on the report for several weeks, it may indicate a tuning opportunity. If a new statement appears, it may be because something changed, such as greater data volume or a different access path.
Top "n" reports are typically customized by application type and category. On-line applications are typically short-lived, while batch applications may run for hours, so these categories should be reported separately.
Most of the SMF-based reports can be requested to display a limited number of items, so-called “Top n”. For example, you can request analysis of all packages during a time period, but only to report on the ten (or twenty, or fifty, or whatever) with the highest value of In-DB2 CPU Time, or Class 1 Wait Time, or any of a number of fields. Here is a list of some of the most useful fields:
Four Common Tuning Tactics
With historical reports in-hand, there are four common methods of reviewing the information in order to performance tune an application. These are:
Of these, the accounting report drill-down is the most frequently used.
Accounting Report Drill Down
Beginning with Accounting Short Report, the DBA scans entries for applications and looks for current and potential performance issues. These might include high CPU usage, long elapsed times, high number of SQL statement issued, or a high number of I/Os.
With a few target applications identified, the DBA now reviews the Accounting Long Report. The Short Report entries take only a few lines; in contrast, an entry for an application on the Long Report can take ten pages or more! Limit execution of the Long Report to target applications only.
Where to Begin
No matter what areas of tuning you decide to begin with, one thing is certain: you will have a lot of work to do! To reduce your workload you should concentrate on automating any reporting or analysis processes. One good place to begin is to set up, review, and automate your “Top n” reports.
Chose the the report or reports relevant to your tuning area and implement a regularly-scheduled process to produce the reports. Schedule regular meetings to review the results with the appropriate people.
For example, if you are concentrating on SQL tuning, set up regular reports of your Top Ten (or fifty) SQL statements. Chose your criteria for reporting: Total elapsed time, total CPU time, and total synchronous I/O wait time are common items. Run these reports daily or weekly and review them with your peers or with the proper application area.
Review available health checks and/or develop your own. Run them regularly and review them. Develop plans to address issues when they arise.
Even the most frugal shops have tools that may help you. Discover what IBM and third-party tools exist and determine if they can help you get your job done. Pick what looks like the most useful tool and learn it.