Additional Estimation Considerations - Relational Database Index Design and the Optimizers - page 273

Identifying These Cheap Random Touches
When considering the first two categories (small tables and hot spots), it is
important to have some idea of the pool residency time. This can be measured
by a program that accesses a single page table every N seconds. The number
of synchronous reads will reveal whether the page stayed in memory, and the
average time per synchronous read will indicate whether the page stayed in read
cache. The pool residency time varies considerably according to the workload.
It is especially sensitive to massive batch jobs that perform a large number of
random touches to a large table. Nevertheless, it should be possible to be able
to find an order of magnitude for heavy loads. If the value is less than a minute,
the pool is relatively small.
When considering the third category (comebacks), the size of the index or
table should first be compared with the pool size. Is the difference large enough
for the rest of the concurrent load? The second criteria is the average time between
accesses to a page.
The fourth category (resident tables and indexes) is trivial, but the effect of
restarting the system with an empty pool should not be forgotten. The optimizers,
by the way, are not very good at estimating pool hits . They may not even see
the fourth category.
Assisted Random Reads
We saw in Chapter 2 that there are several occasions when random reads will
cost less than the figure assumed by the QUBE. We may now estimate how much
faster the assisted random reads may be.
Skip-Sequential
Skip-sequential read means reading nonconsecutive rows in one direction, such
as reading row 5, row 18, row 20. If the DBMS reads every other row, the time
per row will be almost as short as with sequential read. On the other hand, if
it reads three widely separated rows from a one-million-row table, the time per
row will be almost as long as with random read.
There are two kinds of skip-sequential reads; we will call them natural born
and optimizer generated . An example of the first kind is a singleton SELECT,
which is repeatedly executed with host variable values that are in sequence but
not consecutive. A second example, taken from Chapter 8, is discussed in more
detail below. The second type occurs when the optimizer decides to collect a
list of pointers from an index and sorts them before accessing the table rows.
This can happen with single-table SELECTs using nonclustering indexes—list
prefetch or during a join operation using a hybrid join, both features used by
DB2 for z/OS.