Database Reference
In-Depth Information
2000to2005
Spike reports have little value if nobody has time to analyze them and draw
the right conclusions. In the last few years, perhaps encouraged by a serious
worldwide shortage of DBAs, the demand for self-tuning DBMSs has become
more pronounced. Among the first steps toward this ambitious goal are tools
that propose table and index reorganizations or buffer pool sizing. Advisors for
updating statistics for the optimizer, possibly including recommendations (e.g.,
histograms) per index and table, are expected in the near future. The next step for
such a tool might be the reassessment of the access paths based on filter factors
measured at run time, in order to start processing over again with an improved
access path. This is, after all, how intelligent people behave in many situations.
The spike report, currently used in several DB2 installations, draws atten-
tion to transactions that appear to be access path culprits or lock victims. These
indicators are valuable in enabling busy database specialists to quickly and eas-
ily detect new performance problems arising from changes relating to program
maintenance, database growth, or changes in usage patterns.
LRT-LEVEL EXCEPTION MONITORING
Averages per Program Are Not Sufficient
A report showing the average local response time per program reveals the pro-
grams that are consistently slow. The usefulness of averages is limited, however,
because one program often contains several different functions; even the duration
of one function may vary a great deal depending on the input, such as whether
it is for the average customer or the largest customer.
Monitoring individual transactions should be done as soon as possible,
preferably during the test phase, in order to catch the worst input response times.
Furthermore, it is much easier to analyze the profile of an individual transaction
than the average profile of many different transactions using the same program;
the latter is often as confusing as a comparison of the average characteristics of
a few trucks and a lot of bicycles.
Exception Report Example: One Line per Spike
The information we recommend for an exception report that produces a single
line for each exception transaction, a spike report, is shown below. We will use
the term spike in this chapter to represent a single occurrence of a transaction
that has been detected by the exception monitor. There may well be many other
occurrences of the same transaction that are performing perfectly satisfactorily.
Identification
ΕΎ Program name
Search WWH ::




Custom Search