12c Parallel Execution New Features: Parallel FILTER Subquery Evaluation – Part 2: Distribution Methods

Picking up from the first part of this instalment I'll focus in this post on the available distribution methods for the new parallel FILTER subquery feature.In this post I won't go into the details how the optimizer selects the distribution method automatically - this will be covered in the last part.Here I merely describe the different available methods and how to control them using the new PQ_FILTER hint, which is also mentioned in (more...)

Parallel Projection

A recent case at a client reminded me of something that isn't really new but not so well known - Oracle by default performs evaluation at the latest possible point in the execution plan.So if you happen to have expressions in the projection of a simple SQL statement that runs parallel it might be counter-intuitive that by default Oracle won't evaluate the projection in the Parallel Slaves but in the Query Coordinator - even (more...)

12c Parallel Execution New Features: Parallel FILTER Subquery Evaluation – Part 1: Introduction

12c introduces another interesting new Parallel Execution feature - the parallel evaluation of FILTER subqueries. In pre-12c FILTER subqueries always had to be evaluated in the Query Coordinator. This had several consequences, in particular the data driving the FILTER subquery always had to flow through the Query Coordinator, and hence represented a forced serial execution part of a parallel execution plan. This limitation also meant that depending on the overall plan shape the parallel plan (more...)

Video Tutorial: XPLAN_ASH Active Session History – Part 6

The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

More parts to follow.

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 (more...)

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. However, in general starting from 12c this new operator usually will get used when it comes to executing a serial part of the execution plan, like a full scan of an object not marked parallel, or an index based operation that can't be (more...)

Temp Table Transformation Cardinality Estimates – 2

Continuing from the previous part - which was about the Temp Table Transformation and join cardinality estimates - using the same simple table setup here is a slight variation of the previously used query to demonstrate the potential impact on single table cardinality estimates:

explain plan for
with
cte as (
select /* inline */ id from t1 t
where 1 = 1
)
select /*+
no_merge(a) no_merge(b)
*/ * from cte a, cte (more...)