“Parallel Execution Plans” - April 15, 2014
When you read an execution plan you’re probably trying to identify the steps that Oracle went through to acquire the final result set so that you can decide whether or not there is a more efficient way of getting the same result.
For a serial execution plan this typically means you have to identify the join order, join methods and access methods together with the point at which each predicate was applied. Generally this isn’t too difficult although there are a few cases where the difference between join order and the order of operation can be slightly obscured.
Parallel execution plans are harder, though, because you really need to understand the impact of the order of operation, distribution mechanisms chosen, and (in recent versions of Oracle) the timing of the generation and use of Bloom filters.
In this presentation we examine the basics of how parallel execution slave work, and the way in which this can result in a massive difference between the join order of an execution plan and the order of operation of the steps of that plan. We learn about “table queues” and how they help us follow the order of operation, and how the distribution method can make a dramatic difference to performance – following which we then learn how to use hints to optimize parallel queries.