Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Init. param. OPTIMIZER_PERCENT_PARALLEL > 0 ?
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...Received on Mon Nov 10 2003 - 02:46:27 CST
> 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