Optimizers Are Not Perfect - Relational Database Index Design and the Optimizers - page 258

number of pages read sequentially exactly. Some optimizers do have problems in
estimating the effect of prefetch, however, especially when parameters are used
to limit the number of prefetched pages.
The optimizer then simply needs the coefficients for the two types of I/O,
such as 10 ms per random read and 40 MB/s for sequential read. The first value
is an adequate estimate for the drives currently in use, but the speed of sequential
read varies widely. It may be 2 MB/s for really old disk servers and 80 MB/s
for the latest ones. Now there are three alternatives:
1. The optimizer developers decide on a typical disk system for each DBMS
version and choose the coefficients accordingly. DB2 for z/OS (only used
on mainframes) and SQL Server 2000 (only used with Windows) currently
seem to have this approach.
2. The optimizer provides external parameters that can be used to specify
the characteristics of the disk system. DB2 UDB for LUW (used on many
different platforms) is built in this way.
3. The utility that collects the statistics may measure the I/O speed and
store the values in the System Statistics. This approach is implemented
in Oracle 10 g (also used on many different platforms).
This is not a minor point. If the optimizer assumes 4 MB/s for sequential
read while the actual speed is 40 MB/s, the I/O time estimate to scan a 400-MB
table will be 100 s, whereas the actual time will only be 10 s. Consequently,
assuming the CPU time is ignored, the optimizer would consider the break-even
point for choosing between a full table scan and a nonfat nonclustering index to
be FF = 1% (10,000 random reads) while the actual break-even point would be
FF = 0 . 1% (1000 random reads).
Buffer pool and cache hits are also important issues that make it difficult to
estimate I/O time. Our quick estimates (the QUBE) are often pessimistic because
every random touch is assumed to take 10 ms. As memory becomes ever cheaper,
a requested table or leaf page is more likely to be in the database pool or in the
read cache. For applications with which one is familiar, it may be possible to
estimate which pages are accessed so frequently that they are likely to remain in
the pool. It would then be possible to add a third parameter type of touch to the
QUBE formula— a cheap random touch , costing 0.1 ms.
Evaluating the impact of pool or cache hits though is tedious for us and
very difficult for the optimizers. The current optimizers may assume that really
small indexes and tables stay in the buffer pool, but in general their estimates
about pool behaviour are almost as rough as those of the QUBE. The exact
algorithms are confidential and they are sometimes quietly changed, but the main
assumption seems to be that only the root page remains in memory. This explains
a fairly common problem, namely, that the optimizer may choose a wrong index
if the right index, often a fat one, happens to have one more level than the
chosen index.