Re: maxthr system statistic

On 10.2.0.2 this time ...

With maxthr=4000:

parallel=1 cost=5102
parallel=2 cost=4155
parallel=3 cost=4155
parallel=4 cost=4155
parallel=5 cost=4155
parallel=6 cost=4155
parallel=7 cost=4155
parallel=8 cost=4155

With maxthr=8000:

parallel=1 cost=5102
parallel=2 cost=2819
parallel=3 cost=2078
parallel=4 cost=2077
parallel=5 cost=2077
parallel=6 cost=2077
parallel=7 cost=2077
parallel=8 cost=2077

With maxthr=16000:

parallel=1 cost=5102
parallel=2 cost=2819
parallel=3 cost=1879
parallel=4 cost=1409
parallel=5 cost=1127
parallel=6 cost=1039
parallel=7 cost=1039
parallel=8 cost=1039

Notice how the limiting value gets halved as we double maxthr:

4155 -> 4155 / 2 = 2077 -> 2077 / 2 = 1039

which is reasonable - doubling the max throughput means halving the time it takes for the blocks to hop from the disks to the buffer cache when we have enough "parallel servers" to saturate the disk bandwidth.

HTH :) Alberto

On 1/22/07, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
> Very interesting but I'm not quite sure I understand the correlation
> between maxthr and the cost calculation from your results below. Did
> the point at which the DOP/cost ratio flattened out change with
> different values of maxthr? In other words, if you doubled maxthr to
> 16000, did the cost continue to decrease until you got to parallel=8?
>
> Thanks,
> Brandon
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Alberto Dell'Era
>
> the cost doesn't change beyond a certain degree of parallelism, since
> IMHO after that point (parallel=4 in this case) the CBO assumes that the
> disks are running at 100% utilization (aka Max Throughput) and so adding
> new parallel servers won't decrease the response time (aka cost).
>
> Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
>
>

-- 
Alberto Dell'Era
"Per aspera ad astra"
--
http://www.freelists.org/webpage/oracle-l