Re: maxthr system statistic
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 20 Jan 2007 23:05:57 +0100
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:
