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

Synchronous reads (type 1) do not overlap CPU time; the application program
stops and waits for the arrival of the requested page from the disk server. The spike
report shows the number of pages the transaction read synchronously together with
the average wait time (the suspension time) for these synchronous reads.
Asynchronous reads (types 2 and 3) are prefetch reads; the I/O time does
overlap the CPU time of the program; the DBMS asks for the next few pages from
the disk server while the program is still processing pages in the database buffer
pool. The program may or may not run out of pages to process. If it does, the wait
time is recorded in the Wait for Prefetch counter. If the program never has to wait
for prefetched pages, it is CPU bound. The Wait for Prefetch component in the
spike report is then zero, and the elapsed time for the sequential scan is reported
partly as SQL CPU Time and partly as Other Wait (for any CPU queuing).
Skip-sequential read (type 2) tends to be I/O bound with current hardware.
Wait for Prefetch may be the largest component of the local response time if most
of the skips are large, meaning that the pages containing the qualifying rows are
far from each other.
Sequential read (type 3) is roughly balanced with current hardware; some-
times the CPU time per page is a bit longer than the I/O time per page, sometimes
a bit shorter. The QUBE assumes that the larger of the I/O and the CPU times
is up to 0.01 ms per row. If sequential read is I/O bound, the elapsed time of
a sequential scan is equal to the disk I/O time, which in turn is the sum of the
CPU Time and the Wait for Prefetch components of the spike report.
Many programs access table pages in more than one of these three ways.
Interpreting the spike report is then not so straightforward, but the dominant
method is normally not too hard to deduce. The total number of table pages
accessed may help. This contains, in addition to the synchronous reads, pages
read by prefetch (both types 2 and 3) as well as buffer pool hits (the table pages
found in the database buffer pool).
Many DBMSs are nowadays able to perform sequential processing in parallel,
for instance, by breaking a cursor into several internal cursors, each using one
processor and creating its own prefetch stream. If this option (CPU and I/O
parallelism) is enabled, it is normally the optimizer that determines the degree of
parallelism. If there are enough processors, disk drives, and buffer pool space,
the degree may be perhaps 10. The elapsed time may then, theoretically, be
almost as low as 10% of the nonparallel elapsed time. This option is invaluable
when scanning massive tables and their indexes, data warehouse fact tables,
for instance, but most installations with traditional operational transactions and
batch jobs disable parallelism because a program that exploits several processors
and drives at the same time may cause unacceptable queuing times for other
concurrent users.
Tuning Potential
Before spending a great deal of time analyzing an exception transaction, it is
worthwhile estimating by how much the local response time may be reduced as