Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Init. param. OPTIMIZER_PERCENT_PARALLEL > 0 ?

Re: Init. param. OPTIMIZER_PERCENT_PARALLEL > 0 ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 10 Nov 2003 08:46:27 -0000
Message-ID: <bonj6k$pl0$1$830fa78d@news.demon.co.uk>

Notes in-line

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Spendius" <spendius_at_muchomail.com> wrote in message
news:aba30b75.0311060733.1b500410_at_posting.google.com...

> Hi,
> I'd just like to know whether you need to set the
> OPTIMIZER_PERCENT_PARALLEL parameter to a value different
> from its default (0) in order for the optimizer to take
> the tables' degree of parallelism in account.
>
No, this parameter simply affects which execution path is finally taken - the degree of the underlying objects is always recognised by the optimizer
> If yes, this OPTIMIZER_PERCENT_PARALLEL value can be set
> between 0 and 100: but what are the meanings of figures
> as different as 12, 44 or 87 ?!? Here again the Oracle
> doc. is pretty obscure about the details.
>
When evaluating the cost of a particular execution path, the optimizer calculates two figures for each step - the serial cost, and the parallel cost at maximum degree. The final cost of a path is then taken using a straight line interpolation between these two figures - so if you set opt_pct_para to 27, then the cost of a path would be 27% of the way from the highest to the lowest. In effect, a) when opt_pct_para is zero, Oracle 'assumes' that it will be taking a serial path at run time, and optimises for that (which means that if it executes that path in parallel it may be very bad news for your machine). b) when opt_pct_para is 100, Oracle 'assumes' that it will be able to collect all the parallel query slaves that it wants, and there will be no contention between the slaves when the query runs, and picks a plan that will perform best in these circumstances. (This could be even worse news for you machine). c) anywhere in between, and you are giving Oracle the option of finding a path that should be the best path if your run-time degree of parallelism is choked back to that percentage of your requested degree because of a lack of available slaves. Except in very special cases (low number of users, large number of devices, reasonable number of CPUs) it is probably sensible to leave the parameter at the default values - note that is goes hidden in 9.2.
> Thanks a lot.
> Regards,
> Spendius
Received on Mon Nov 10 2003 - 02:46:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US