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

In the first case the average number of disk reads per random touch was

845 04; in the second case there were no disk I/Os at all. This result

supports a common and important observation: Eliminating random reads from
a disk cache or drive saves a significant amount of CPU time. It also illustrates
one of the factors that cause a large variation in the CPU time per random touch.
According to our experience, the CPU time consumed by a random touch is
often in the following range with current hardware:

/ 814 = 1 .

Table touches: between 10 and 100 µ s
Index touches: between 20 and 300 µ s

The largest cause of this enormous variation with random touches appears to be
the high-speed CPU cache. When a burst of random touches occurs to a small
number of pages in memory, the CPU time per random touch may be even
less than 10 µ s; in fact, it can approach the level of sequential touches (a few
microseconds).

Conclusion

The coefficient of 100 µ s CPU per random touch still seems valid for quick
estimates. However, because of the large variations, as discussed above, an appro-
priate range of values should be used whenever an important decision, such as
table denormalization or adding a new index, is being made based on CPU time
estimates. For instance, the coefficient could be assumed to be between 100 and
300 µ s for truly random touches to a large index.
Unfortunately, this could well cause problems when estimating CPU potential
savings; if an SQL statement with 10,000 random touches consumed 600 ms of
CPU time, eliminating 9000 random touches couldn't possibly save 900 ms of
CPU time. We would have to assess the proportion of the 600 ms that was due
to TRs.

CPU Time per FETCH Call

The CPU time per FETCH call itself (excluding the touches) depends on the
platform and the way in which the application program and the DBMS are con-
nected. In multitier environments and with some transaction managers, the cost
of shipping the SQL call to the DBMS (and shipping the result back) can con-
sume from 50 to 100 s of CPU time. In other environments, especially when

the SQL calls are embedded in a batch program running in the database server,
the CPU time per FETCH may be between 10 and 20

µ

s.
Figure 7.9 shows a transaction that issued approximately one million SQL
calls with an efficient access path; most of the calls caused only one sequential
touch. The total CPU time was about 100 s when the CPU time consumed by
the transaction manager (CICS) is taken into account; about 100

µ

s per FETCH.

µ