Re: maxthr system statistic

Brandon, Jonathan,

On 1/20/07, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> 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"



--
http://www.freelists.org/webpage/oracle-l




application/zip attachment: maxthr.zip