Re: 11g: select with union all from two different table function not parallel
From: Frank Bergemann <FBergemann_at_web.de>
Date: Thu, 23 Feb 2012 01:06:10 -0800 (PST)
Message-ID: <0bacfc58-abff-4c84-90ec-46de2578dd44_at_i2g2000vbv.googlegroups.com>
On Feb 23, 9:50 am, Randolf Geist <i..._at_sqltools-plusplus.org> wrote:
> On 22 Feb., 03:20, Frank Bergemann <FBergem..._at_web.de> wrote:
>
>
>
> > But what abouthttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2...
> > Shouldn't 'union all' allow parallel execution?
>
> And which part of the AskTom thread do you think confirms your theory?
> Oracle supports Parallel Execution but not in the way you think /
> hope.
>
> Oracle can perform parts of the execution plan in parallel by multiple
> slave sets but it is based on producer / consumer model having at most
> one producer and one consumer active at the same time (within the same
> so called "Data Flow Operation" (DFOs) - there can be multiple DFOs
> active at the same time but not in such a way as you have in mind).
> Hence your idea of two parallel slave sets executing the two branches
> below the UNION operator concurrently as producers and feeding the
> results into a third consumer is (currently) not supported.
>
> Even with Parallel Execution enabled the rule outlined is still valid:
> If the UNION is executed using Parallel Execution, the first child
> operation will be executed first in parallel by multiple slaves and as
> soon as this is completed, the second child will be started, and so
> on.
>
> Remember that the UNION operator (and other operators like FILTER,
> UPDATE, DELETE etc.)can have an arbitrary number of child operations,
> how many of them would you like Oracle to consider running
> concurrently? Therefore Oracle uses a different approach to Parallel
> Execution that is independent from such considerations.
>
> Hope this helps,
> Randolf
Date: Thu, 23 Feb 2012 01:06:10 -0800 (PST)
Message-ID: <0bacfc58-abff-4c84-90ec-46de2578dd44_at_i2g2000vbv.googlegroups.com>
On Feb 23, 9:50 am, Randolf Geist <i..._at_sqltools-plusplus.org> wrote:
> On 22 Feb., 03:20, Frank Bergemann <FBergem..._at_web.de> wrote:
>
>
>
> > But what abouthttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2...
> > Shouldn't 'union all' allow parallel execution?
>
> And which part of the AskTom thread do you think confirms your theory?
> Oracle supports Parallel Execution but not in the way you think /
> hope.
>
> Oracle can perform parts of the execution plan in parallel by multiple
> slave sets but it is based on producer / consumer model having at most
> one producer and one consumer active at the same time (within the same
> so called "Data Flow Operation" (DFOs) - there can be multiple DFOs
> active at the same time but not in such a way as you have in mind).
> Hence your idea of two parallel slave sets executing the two branches
> below the UNION operator concurrently as producers and feeding the
> results into a third consumer is (currently) not supported.
>
> Even with Parallel Execution enabled the rule outlined is still valid:
> If the UNION is executed using Parallel Execution, the first child
> operation will be executed first in parallel by multiple slaves and as
> soon as this is completed, the second child will be started, and so
> on.
>
> Remember that the UNION operator (and other operators like FILTER,
> UPDATE, DELETE etc.)can have an arbitrary number of child operations,
> how many of them would you like Oracle to consider running
> concurrently? Therefore Oracle uses a different approach to Parallel
> Execution that is independent from such considerations.
>
> Hope this helps,
> Randolf
Hi Randolf,
okay, good to know.
I was meanwhile testing with plain tables (instead of table
functions)
- the same result.
So it is not a matter of table functions.
And i have to find another way.
- thanks for clarification!
best regards,
Frank
Received on Thu Feb 23 2012 - 03:06:10 CST