Parallel Execution

randolf.geist's picture

12c Parallel Execution New Features: 1 SLAVE distribution

When certain SQL features get used in pre-12c versions that force non-parallel evaluation, like using ROWNUM or certain Analytic Functions like LAG/LEAD, then - depending on the overall plan shape - Oracle can start to decompose the parallel execution plan into several so called DFO trees (If you want learn more about DFO trees and DFOs I recommend watching my online tutorial on my Youtube channel).Now having multiple DFO trees in a single parallel execution plan comes with several side effects that are confusing and complicate matters unnecessarily, like each DFO tree allocates its own PX slave set(s), and so each one can potenially end up with a different DOP, which means you can have more than one DOP in a single parallel execution plan.Depending on the overall plan shape this might also mean that a DFO tree can get started multiple t

randolf.geist's picture

12c Parallel Execution New Features: PX SELECTOR

Continuing my series on new 12c Parallel Execution features: I've already mentioned the new PX SELECTOR operator as part of the new Concurrent UNION ALL feature where it plays a key role.

Jonathan Lewis's picture

Parallel Query

According to the Oracle Database VLDB and Partitioning Guide (10g version and 11g version):

A SELECT statement can be executed in parallel only if the following conditions are satisfied:

Jonathan Lewis's picture

Parallel Execution

This is another little reference list I should have created some time ago. It covers a series of posts on interpreting parallel execution plans and understanding where the work happens.

randolf.geist's picture

DOAG Expertenseminar "Parallel Execution Masterclass" (German)

In zwei Wochen findet das Expertenseminar "Parallel Execution Masterclass" in Berlin statt, das ich gemeinsam mit der DOAG veranstalte.

Es sind noch ein paar Plätze frei - sollten Sie also Lust und Zeit haben, nach Berlin zu kommen und exklusives Wissen (nicht nur) über das Parallel Execution Feature der Oracle Datenbank zu erfahren, würde ich mich sehr freuen, Sie dort mit den anderen Teilnehmern begrüßen zu dürfen, um gemeinsam mit Ihnen eine gute und produktive Zeit zu verbringen!

Bei Interesse wenden Sie sich bitte an die Ansprechpartner der DOAG, die im Link angegeben sind - dort finden Sie auch eine genauere Beschreibung des Seminars.

randolf.geist's picture

12c Parallel Execution New Features: Concurrent UNION ALL - Part 3

In the final part of this instalment I want to focus on the possible optimisation of remote access that I outlined in the initial part, which is based on the idea of running multiple concurrent remote branches of a UNION ALL to overcome the query coordinator bottleneck of straightforward remote queries that need to transfer larger amounts of data.For that purpose I now simply change my sample query to access the serial table T2 via the DB link defined in the setup of the initial part, like that:


set echo on timing on time on

select /*+ PQ_CONCURRENT_UNION(@"SET$1") */ count(*) from (
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2@loop
randolf.geist's picture

12c Parallel Execution New Features: Concurrent UNION ALL - Part 2

In the first part of this series I've focused on the parallel degree chosen by the optimizer when dealing with the new concurrent UNION ALL feature.I've shown that for the variant with serial branches only in the UNION ALL in principle the number of branches dictates the parallel degree determined, even in cases of more complex plans that mix such a serial branch only UNION ALL operator with some other parallel stuff for example via a join.In this part I'll focus on the runtime behaviour of the feature, but before doing so let me show you what happens if you start mixing serial and parallel branches in the UNION ALL, like that (using the identical table setup as in the previous part):


select count(*) from (
randolf.geist's picture

12c Parallel Execution New Features: Concurrent UNION ALL - Part 1

12c introduces the concurrent UNION ALL operator that allows multiple branches below the UNION ALL to become active concurrently - which is a significant change. Before the introduction of this feature Oracle never executed multiple branches of an execution plan concurrently (in terms of Parallel Execution) - the parallelism so far was about executing the same operations of a particular branch of the execution plan by multiple processes / sessions.

randolf.geist's picture

12c Parallel Execution New Features: Hybrid Hash Distribution - Part 2

In the second part of this post (go to part 1) I want to focus on the hybrid distribution for skewed join expressions.

2. Hybrid Distribution For Skewed Join Expressions

The HYBRID HASH distribution allows to some degree addressing data distribution skew in case of HASH distributions, which I've described in detail already in the past.

randolf.geist's picture

12c Parallel Execution New Features: Hybrid Hash Distribution - Part 1

In this blog post I want to cover some aspects of the the new HYBRID HASH adaptive distribution method that I haven't covered yet in my other posts.As far as I know it serves two purposes for parallel HASH and MERGE JOINs, adaptive broadcast distribution and hybrid distribution for skewed join expressions. In the first part of this post I want to focus on former one (goto part 2).

1. Adaptive Broadcast Distribution For Small Left Row Sources

It allows the PX SEND / RECEIVE operation for the left (smaller estimated row source) of the hash join to decide dynamically at runtime, actually at each execution, if it should use either a BROADCAST or HASH distribution, and correspondingly for the other row source to use then either a ROUND-ROBIN or a HASH distribution, too.

Syndicate content