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

1. Almost every random touch causes a page request.
2. The I/O-related CPU time, when the page is not in the buffer pool, tends
to be high. The I/O cost of a sequential read per page is lower because
of multipage reads. In addition, the cost per page is shared by many
sequential touches.
3. Significant memory waits may occur because the CPU cannot prefetch
pages into the high-speed CPU caches; by definition, random touches are
unpredictable.
4. A random touch to an index, as defined by the QUBE, ignores the nonleaf
pages because they are assumed to be in the buffer pool. For CPU time,
however, accessing an index row in a three-level index randomly normally
causes three page requests. This is why random index touches consume
more CPU time than random table touches.

One way to easily determine the CPU time per random touch is simply
to compare the CPU time before and after unproductive random touches are
eliminated with semifat or fat indexes.
A second way would be to eliminate the cheaper components (e.g., sequential
touch and sort). To illustrate this approach, a nested loop join SELECT with 813
random touches to a three-level index, had the following profile in an installation
with 100 mips processors:
TR = 814 ( medium index rows )

Disk Reads = 845 ( random reads from disk )

TS = 8130 ( short index rows )

F = 814

RS = 0

CPU time = 401 ms

The CPU time per random index touch can be deduced by using the
installation-calibrated coefficients for TS and F:
401 ms − [ ( 8130 × 1 µ s ) + ( 814 × 50 µ s ) ]
814

= 434 µ s

s
2 . 5 = 173 µ s
This CPU time was measured in a test environment with a cold pool (no
hits in the pool or disk cache, 845 random reads from disk). When the same
transaction was executed again quite soon after the first measurement, the CPU
time was 165 ms. In this case, assuming the CPU cost of the other components
remains at 48 ms, the CPU time per random touch becomes
165 ms − 48 ms
814

430 µ

Converted to 250 mips

= 144 µ s

144 µ s
2 . 5 = 58 µ s

Converted to 250 MIPS