Re: maxthr system statistic

Brandon, Jonathan,

On 1/20/07, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote:

I believe that Oracle then uses these values in some
way to adjust the cost of parallel queries by throwing
in a fudge factor that chokes the degree used if it
looks "optimistic" compared to
    maxthr /slavethr.


I've run a modified version (see attach) of the parallel_2.sql script
contained in ch. 2 of Jonathan's "Cost Based Oracle",
changed only by setting the MAXTHR system statistic:

dbms_stats.set_system_stats('MAXTHR', 8000);

I've noticed this (on 10.2.0.1):

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

that is, 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).

HTH
Al

--
Alberto Dell'Era
"Per aspera ad astra"

Other related posts: