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

a result of planned index improvements. The tuning potential is the upper limit
for the achievable reduction.

RandomReads

The tuning potential of read only transactions is the product of the number of
table pages read synchronously and the average duration of a synchronous read.
The local response time will be reduced by this amount if all table page reads
are eliminated by means of fat indexes. Thus, in Figure 7.5, the tuning potential
is 50 , 331 × 9ms = 453 s if the program is read only; programs with INSERT,
UPDATE, or DELETE calls have a smaller tuning potential because the table
pages must be updated no matter how fat the indexes.

Skip-SequentialReads

Some optimizers would choose skip-sequential read in the previous example. The
DBMS would then first collect the pointers from all qualifying index rows and
then sort the pointers by table page number. Now the DBMS has a sorted list
of all the table pages that contain at least one qualifying row; only now will the
table be accessed. Naturally, the I/O time per page will be shorter, especially
if several qualifying rows happen to be on the same track. In addition, the I/O
time will overlap the CPU time because the DBMS is able to read ahead. The
saving compared to synchronous reads is very variable; it depends on the average
distance between the pages to be read and on the striping implementation; the
tuning potential is simply the current value for Wait for Prefetch (assuming the
program is read only and all prefetch is skip-sequential, e.g., list prefetch in DB2
for z/OS). Thus, the tuning potential in Figure 7.6 is 102 s.

LRT

Synchronous read
50,342 × 9 ms
= 453 s

SQL calls
1003

455 s

Indexes:
Tables:

11
50,331

SQL Non- SQL

455 s 0 s

0 s

2 s 453 s 0 s 0 s

Wait for
prefetch

CPU time Sync

read

Lock
waits

Other
waits

Figure 7.5 Promising culprit—random reads.