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
 
 
Search WWH ::




Custom Search