oracleoracle-12cparallelismperformanceperformance-tuning
I am reading an article about Understanding Parallel Execution
There is a paragraph which is as follows:
But for my understanding of how to read an execution plan, this parallel execution plan starts from operation 7 not operation 14.
Why this article emphasize that the execution plan starts at operation 14?? It upside down my knowledge of execution plan.
Does the bloom filter prevents all rows or some rows from table FACT that do not join table DIMENSION from being needlessly distributed?
Look at Antognini's article! A bloom filter does not prevent false positives. This means exactly that a "bloom filter prevents [...] [only] some rows from table FACT that do not join table DIMENSION from being needlessly distributed". Of course there may be situation where it avoids false positive. This is the case in the example of Joe Obbish: If one has to filter 1 value from only 25 possible values this can be done without false positives with a bloom filter.
Is the bloom filter created in parallel by the set of parallel execution processes that scanned table DIMENSION or the set of parallel execution processes that later perform hash join?
To create the bloom filter one has to scan the table DIMENSION and add every COL2 value to the filter.
Look at Antognini's plan at p.5:
You can see that the PX JOIN FILTER CREATE step is done by the Q1,02 process group.
This is the group that is responsible for joining the data and not the group Q1,00 that scans the table T1.
My original understanding is (likely incorrect) a scheduler may (when there is a need) use multiple workers/threads from the same NUMA node to process different tasks of the same execution request when it is a serial execution.
A serial plan will not use multiple threads it will only use single thread to perform the task. When the serial query would start it would be bound to a scheduler and that scheduler will not perform any other task unless it completes this query. The scheduler may wait for a process to complete thus forcing query to wait.
It is also worthy noting that MAXDOP setting controls the amount of thread used for parallel processing and MAXDOP is always specified per operator in the execution plan and not per execution plan
I strongly suggest you read below article by Paul White
Best Answer
According to the quoted text Oracle will only have two parallel active per data flow operation. Take a look at the TQ column of the EXPLAIN PLAN. There is only one data flow operation for this query (Q1). There are five different parallel slave sets for Q1:
Q1,00andQ1,01will be processed first. The operation on line 7 (FTS on T3) has a slave set ofQ1,02so it can only start afterQ1,00orQ1,01finish and free up a slave set.