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

ž The name of the dominant module (contributing most to the LRT)
ž The contribution of the dominant module to the LRT (%)
ž Date
ž End time (hh.mm.ss)
TransactionProfile
ž Local response time (s) LRT
ž Total elapsed time of the SQL calls (s) SQL
ž CPU time of the SQL calls (s) CPU time
ž Synchronous read time (s) Sync read
ž Wait for prefetch time (s) Wait for prefetch
ž Lock wait time (s) Lock waits
ž Other wait time (s) Other waits
ž Average duration of synchronous reads (ms)
ž The number of synchronous reads (table pages)
ž The number of synchronous reads (index pages)
ž The number of executed SQL calls
ž The number of table pages accessed
ž The number of index pages accessed
ž The number of sequential prefetch requests
ž The number of commit points
ž Quick diagnosis (derived from the above figures)
The terms highlighted to the right of the first seven items of the transaction profile
(e.g., LRT ) refer to the terms shown in the bubble charts used in this chapter.
An example of a transaction that would be identified during exception mon-
itoring, showing these transaction profile components, is shown in Figure 7.1.
Displaying the information from the exception report in this way makes it very
easy to identify any problems.
A discussion on the meaning, interpretation, and any resultant tuning of these
numbers will be found a little later.
Culprits and Victims
A spike report, covering perhaps the peak hours of the week, may contain thou-
sands of spikes from more than a 100 different dominant modules. A database
specialist may be able to analyze and fix, with a little help from application
developers, perhaps 5 to 10 modules per week. The spike report for the follow-
ing week may show new modules because of program changes or different user
input. To productively use the limited time normally available, it is important