Database Reference
In-Depth Information
Formula 7-6.
The computation of the expected throughput (in bytes per second) of a single server process based on
workload statistics and the default block size of the database
mbrc db block ize
mreadtim
⋅
_
_
mreadthr
≈
⋅
1000
In case the query optimizer is too optimistic in estimating the costs of parallel operations, with
slavethr
you can
increase the estimations. To do so, you set
slavethr
to a value lower than
mreadthr
, which is the value computed with
Formula 7-6. In other words, you inform the query optimizer that the throughput that each slave process can sustain
is lower than the default value. Note that the opposite—decreasing the cost by setting
slavethr
to a value higher than
mreadthr
—is not possible. In fact, when the ratio of
slavethr
to
mreadthr
is higher than 0.9 (the fudge factor used in
Formula 7-5), it has no impact on the cost estimated by the query optimizer. Figure
7-4
shows, for a full table scan, the
impact of setting
slavethr
to half of the value of
mreadthr
.
Figure 7-4.
Comparison of the estimated I/O costs with and without
slavethr
(data generated by
the
parallel_fts_costing.sql
script)
The adjustment due to
slavethr
is shown in Formula 7-7. Notice the difference with Formula 7-5: the fudge
factor (0.9) is only used when it's higher than the ratio of
slavethr
to
mreadthr
.
Formula 7-7.
An adjustment (increase) of the parallel I/O costs take place when the ratio of
slavethr
to
mreadthr
is lower than 0.9 (note that k is defined in the note following the formula)
.
serial_io_cost
parallel_io_cost
≈
slavethr k
mr
⋅
dop
⋅
least09 ,
eadthr
■
in Formulas 7-7 and 7-8, the factor
k
depends on the database release. Up to version 11.2.0.3, it has the value
1,000. From version 11.2.0.4 onward, it has the value 1. as a result, through version 11.2.0.3 only very small values of
slavethr
compared to
mreadthr
actually impact the estimations of the query optimizer. For this reason, in practice, most
of the time no impact is observed through version 11.2.0.3.
Note
As Formula 7-7 clearly shows, the costs stay inversely proportional to the degree of parallelism, and therefore