Re: PARALLEL QUERY

Hi Jonathan,

Thanks again for your nice explanation. But I had a little difficulty in understanding the following....

> think about ANY executions that might NOT do a parallel tablescan on table
XXX and make it impossible for them to happen.

Would be great if you can kindly elaborate on the above.

In my original post in this thread Oracle completely ignored the parallel scan of the table even though the explain plan showed that it had lesser cost. As per your explanation I had restricted/limited Oracle optimizer (through hints) to follow parallel processing approach.

Please excuse me for any kind of ignorance.

Regards,

Deepak

On 10/28/07, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>
> Deepak,
>
> Your first comment is (I believe) correct -
>
> > the parallel hint merely reduces the effective execution cost of the
> query
>
> Your conclusion, though, is not valid
>
> > Does this mean we should ideally NOT provide parallel hint as it merely
> > reduces the cost for determining the most efficient execution plan?
>
> I tried to write down a more appropriate conclusion - but it got very
> wordy; so I'll just state the strategy.
>
> Under ANY circumstances, what you are trying to do with hints
> is to restrict the optimizer to having just ONE possible path through
> its own codebase. If you don't use enough hints, or don't use the
> hints properly, then the optimizer may find a way of doing something
> you didn't want, despite obeying all your hints at the appropriate points
> in its working.
>
> So, if you use the PARALLEL(XXX) hint, think about ANY executions that
> might NOT do a parallel tablescan on table XXX and make it impossible for
> them to happen.
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> From: "DBA Deepak" <oracle.tutorials_at_gmail.com>
> To: <jonathan_at_jlcomp.demon.co.uk>
> Cc: <oracle-l_at_freelists.org>
> Sent: Saturday, October 27, 2007 5:53 PM
> Subject: Re: PARALLEL QUERY
>
>
> > Hi Jonathan,
> >
> > Thanks a lot for your valuable input. But is the parallel hint merely
> > reduces the effective execution cost of the query as against the other
> hints
> > like index hint where the optimizer does not bother about the cost of
> the
> > resultant execution plan and biased to use the index mentioned in the
> hint.
> >
> > Does this mean we should ideally NOT provide parallel hint as it merely
> > reduces the cost for determining the most efficient execution plan?
> >
> > Please help me in understanding the things here...
> >
> > Regards,
> >
> > Deepak
> >
> >
> > On 10/17/07, jonathan_at_jlcomp.demon.co.uk <jonathan_at_jlcomp.demon.co.uk>
> >> To the best of my knowledge the /*+ parallel(alias, degree) */ hint
> >> does NOT tell the optimizer to use parallel execution, it merely tells
> >> the optimizer to divide the cost of a tablescan on 'alias' by 'degree'
> >> (allowing for the effect of the _optimizer_percent_parallel in general
> >> and a fixed 0.9 scaling factor in 10g specifically) and then follow the
> >> consequences.
> >>
> >> It is perfectly feasible that the optimizer found a serial index access
> >> path that was cheaper than the 'tablescan cost / 4' dictated by the
> hint.
> >>
>
>

-- 
Regards,

Deepak
Oracle DBA

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