Re: Adding a table to a join removes parallel execution

From: joel garry <joel-garry_at_home.com>
Date: Wed, 5 Jun 2013 09:54:16 -0700 (PDT)
Message-ID: <6787d5d0-8f48-4fea-b5e8-242e364e3c8d_at_lr16g2000pbb.googlegroups.com>



On Jun 5, 7:38 am, Sébastien de Mapias <sglrig..._at_gmail.com> wrote:
> 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

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

--
_at_home.com is bogus.
http://desktops.cbronline.com/news/dell-oracle-expand-global-alliance-050613
Received on Wed Jun 05 2013 - 18:54:16 CEST

Original text of this message