Reactive Index Design - Relational Database Index Design and the Optimizers - page 130

has been made that 300 disk I/Os can be performed per second, and that 4,000
buffer page requests can be performed per second. These times are typical to
medium to high-end machines.
Thus, the recommended queries report the SQL statements that will cause
more than 3,000 disk I/Os or access more than 40,000 database pages
according to the optimizer estimate.
(Ref.2,p.67)
Mark Gurry (2, p. 68) continues, addressing monitoring :
Oracle 8i and later has a great feature that stores information on long-running
queries currently active in the V$SESSION LONGOTS
...
Perhaps the DBA should phone the HROA user and question the statement,
maybe even cancelling it if it is going to run much longer.
Online monitoring may be an adequate approach in data warehouse envi-
ronments and with small and noncritical applications. However, in operational
systems with many users, single programs that become slower and slower may
one day bring the whole database server to a standstill. An exception report
covering all interactive transactions gives an early warning of all kinds of per-
formance problems. Monitoring the response times of individual transactions is
proactive tuning, minimizing the risk of a performance crisis.
As many DBMS suppliers are now investing heavily to make their systems
more self-tuning, it seems likely that the monitoring facilities will be improved
in many products over the next few years.
Spike Report
Spike reports like the ones shown in this chapter are being produced in two ways
in DB2 for z/OS installations.
The starting point is the DB2 Accounting Trace records, classes 1, 2, 3, 7,
and 8. Using DB2PM (DB2 Performance Monitor), a filter command selects the
records relating to interactive transactions whose local response time or SQL time
exceeds alarm limits set by the installation. The profiles of these transactions are
produced automatically, but the production of a concise report, one line per spike,
requires a little program code and a bit of tailoring according to the database
buffer pool setup (which objects are associated with which pools). This may be
done by either writing a REXX program to read the standard report records, or
by saving the trace data of the spikes in three tables (BASE, PACKAGE, POOL),
which are then read by a program to produce the spike report. The second solution
is a better option for the long term because it is less sensitive to changes in the
trace records. Once created, the spike report rows may be saved in a history table
to enable future comparisons to be made easily. Even with continuous exception
reporting, disk space is negligible.