Re: Init. param. OPTIMIZER_PERCENT_PARALLEL > 0 ?

You've understood absolutely correctly.
What's even more surprising - there is a parameter parallel_min_percent (approx.) which says:

    if you can't run at this percentage of the     requested degree of parallelism, don't     run at all. e.g. if DOP is 4, and p_m_p     is 50, then your query should fail if there     are not enough slaves to run parallel 2     when the query starts to run

EVEN when you set the p_m_p to a non-zero value, Oracle optimises the query on the 'fallback' position that the query may have to run parallel - it doesn't try to optimise for the minimum degree that it will be allowed to run at.

Yes - this does mean that a parallel query is not necessarily going to execute in the most efficient way possible for a parallel query. (Unless there is something cunning built into the arithmetic that somehow manages to guarantee that the serial-assumption is irrelevant).

--
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


"Rick Denoire" <100.17706_at_germanynet.de> wrote in message
news:ptuvqvkcmud9ujb9lvkaur1kppa1eoeagv_at_4ax.com...


> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>

> >    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).

>

> Oracle optimizes for serial path and could still execute in

parallel?


>

> Does that mean that one should try to conciliate what Oracle

optimizes


> for with the actual execution plan? So if a table has an option of

> parallel degree 4, then the session dealing with this table should

> have a high opt_percent_parallel? (Assuming parallelity is switch on

> in the init.ora).

>

> According to this statement, if one leaves opt_percent_parallel at

> zero (default), then every time Oracle executes something in

parallel,


> it will follow a plan which was optimized for serial execution,

(i.e.


> will always be "wrong" or inappropriate). Astonishing.

>

> I think I understood your explanation. I am missing the sense of

such


> logic.

>

> Bye

> Rick Denoire