Adding a table to a join removes parallel execution

[Sébastien de Mapias]

Hello,

In 10gR2, we had a view with a four-table join that the optimizer used
to resolve with parallel processing when queried; we've added another
table to the FROM, and now a trace shows that there's no more parallel
execution whatsoever. I can't understand why: before giving further
details here I would like to hear if someone has any idea as to why
the optimizer gets rid of this parallel execution plan (I suspect that
it no more has the time to try other join permutations and realize
it's better with parallel, but i'm not sure, and *important*: the
hints haven't been modified and there is indeed a hint specifying a
parallel degree on a certain table - which is now ignored !).

The WHERE clauses ("select /*+ parallel(...) */ ... from view where
col_a = '...' and col_b = '...') are the same in both versions of this
view. The table added to the view definition is empty and is outer
joined (it's supposed to be populated little by little in the future).

Thanks a lot.
Regards,
Seb

[ddf]

I no longer have 10.2 installed to play with and doing what you report in 11.2 still provides parallel execution.

We will need much more information from you.


David Fitzjarrell

[Sébastien de Mapias]

I've further investigated and refined the cause of my issue: let's
forget the difference between the old version of my view and the
present one (with the extra table in the FROM block). I noticed that
*if I remove* a subquery which appears in the SELECT block, my view
uses parallel execution.

I have a subquery like "(SELECT DECODE (MIN (dr.remark_id), NULL, 'N',
'Y') FROM the_remarks dr WHERE dr.the_num = d.the_num) remark" in the
select list: if I comment it now the optimizer uses parallel process,
if I leave it, it doesn't. The 'd' alias refers to the table in the
FROM block on which the parallel hint is specified.

So you have:
> select * from (
> select /*+ parallel (d 4) full(d) */
> ...
> (select decode(min(dr.remark_id), ... dr.the_num = d.the_num) remark,
> ...
> from the d, omstatus os, the_info di, the_tckt dt, summary dsum
> where [...])
> where cola = 'MKLJ' colb = 'POIU';

Funny behaviour isn't it ?
Any idea to put me on the right track ??

Thanks.
Seb

[joel garry]

You might want to google for the use of the 10053 trace as explained
by Wolfgang Breitling, and see https://blogs.oracle.com/optimizer/entry/how_do_i_capture_a
for a new way to turn it on for a sql id.

jg
--
@home.com is bogus.
http://desktops.cbronline.com/news/dell-oracle-expand-global-alliance-050613

[dombrooks]

On Wednesday, 5 June 2013 13:43:55 UTC+1, Sébastien de Mapias wrote:
> there is indeed a hint which is now ignored !)

http://jonathanlewis.wordpress.com/2007/06/17/hints-again/

[sglr...@gmail.com]

On Wednesday, June 5, 2013 6:59:02 PM UTC+2, dombrooks wrote:
> http://jonathanlewis.wordpress.com/2007/06/17/hints-again/

Yes I had read this article; I know that I now have to figure out why the optimizer changes to no-parallel exec when I add this subquery in my SELECT block... Going to have a look at Breitling's paper, hopefully it'll help me understand what goes on.

Thanks for your suggestions.
Seb