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

Estimating CPU Time

This requires a prediction of the number of instructions or cycles required by an
SQL call, and then multiplying this value with a processor-dependent coefficient.
Again, if the optimizer knows the number of random touches, the number of
sequential touches, the number of rows sorted, and the number of result rows,
it could be expected to make a fairly decent CPU time estimate. Actually the
optimizer may take many other factors into account, such as the number and com-
plexity of predicates, but there is one additional factor that reduces the accuracy
of the CPU time estimates: the high-speed caches in memory. If an instruction
or a row is not in this cache, it must be moved from the main memory. This may
involve a wait that is quite long compared to the processor cycle time itself. The
gap between processor speed and memory speed is constantly growing. There-
fore, the high-speed cache hit ratio has an exceedingly significant impact on the
actual CPU time. This hit ratio depends on the complexity of an SQL call and on
the level of contention. This is why CPU times may be longer at peak time than
in a stand-alone benchmark. It should be noted that the CPU time, as measured
by the DBMS monitors, includes this memory wait time , yet we normally con-
sider the measured CPU time to be the service time, as opposed to CPU queuing
time (waiting for an available processor).
How do the optimizers choose the processor-dependent coefficient to estimate
the CPU time? DB2 for z/OS is aware of the CPU model used for doing the cost
estimates and uses an appropriate value. Oracle 8 did not estimate the CPU cost at
all; Oracle 9 has external parameters while Oracle 10 g uses the System Statistics
described above. DB2 for LUW also has external parameters.

Helping the Optimizer with Estimate-Related Problems

OptimizeEveryTime—withActualValuesMovedtoPredicate
Variables

The optimizer may be made to choose the access path every time, when the values
of the host variables in the predicates are known. However, with operational
applications, the CPU time overhead tends to be too high—an overhead that
is increasing not decreasing, as the cost formulas become more sophisticated.
Therefore, this alternative should be chosen only when the optimal access path
depends on the values moved into the host variables (as in SEX

= :SEX in the

Sumo wrestler case).
The implementation of this option is product dependent.
ž In DB2 for z/OS, the package containing SELECT ... WHERE SEX =
:SEX must be bound with parameter REOPT(VARS). Then the optimizer
will recalculate the cost estimate every time, using the actual value moved
to host variable :SEX.
ž To use two different access paths with Oracle,

two SELECTs must

'M and another with WHERE be coded, one with WHERE SEX =