Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> 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-lReceived on Tue Jan 23 2007 - 04:54:08 CST