Single Server DFO

 Another improvement similar to last post is Single Server DFO. While in previous post the serial work was done by the query coordinator, that activity can also be executed by a single member of a parallel group set. 

The SQL again: 

select -- BX_13
       /*+ OPT_PARAM('parallel_degree_policy' 'auto') parallel(4) GATHER_PLAN_STATISTICS monitor */ 
       OBJECT_TYPE, count(*) c
from A_O
where rownum < 25000000 
group by OBJECT_TYPE
fetch first 7 rows only; 
select * from dbms_xplan.display_cursor( format =>'ALLSTATS LAST +PARALLEL');

The execution plan now changes from 

Plan hash value: 3726916758
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |          |        |      |            |
|*  1 |  COUNT STOPKEY                                    |          |        |      |            |
|   2 |   PX COORDINATOR                                  |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)                            | :TQ20001 |  Q2,01 | P->S | QC (RAND)  |
|*  4 |     COUNT STOPKEY                                 |          |  Q2,01 | PCWC |            |
|   5 |      VIEW                                         |          |  Q2,01 | PCWP |            |
|*  6 |       SORT GROUP BY STOPKEY                       |          |  Q2,01 | PCWP |            |
|   7 |        PX RECEIVE                                 |          |  Q2,01 | PCWP |            |
|   8 |         PX SEND HASH                              | :TQ20000 |        | S->P | HASH       |
|*  9 |          COUNT STOPKEY                            |          |        |      |            |
|  10 |           PX COORDINATOR                          |          |        |      |            |
|  11 |            PX SEND QC (RANDOM)                    | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|* 12 |             COUNT STOPKEY                         |          |  Q1,00 | PCWC |            |
|  13 |              PX BLOCK ITERATOR                    |          |  Q1,00 | PCWC |            |
|* 14 |               TABLE ACCESS STORAGE FULL FIRST ROWS| A_O      |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=7)
   4 - filter(ROWNUM<=7)
   6 - filter(ROWNUM<=7)
   9 - filter(ROWNUM<25000000)
  12 - filter(ROWNUM<25000000)

to 

Plan hash value: 2053989564
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                                          | Name     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                   |          |        |      |            |
|*  1 |  COUNT STOPKEY                                     |          |        |      |            |
|   2 |   PX COORDINATOR                                   |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)                             | :TQ10002 |  Q1,02 | P->S | QC (RAND)  |
|*  4 |     COUNT STOPKEY                                  |          |  Q1,02 | PCWC |            |
|   5 |      VIEW                                          |          |  Q1,02 | PCWP |            |
|*  6 |       SORT GROUP BY STOPKEY                        |          |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE                                  |          |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH                               | :TQ10001 |  Q1,01 | S->P | HASH       |
|   9 |          BUFFER SORT                               |          |  Q1,01 | SCWP |            |
|* 10 |           COUNT STOPKEY                            |          |  Q1,01 | SCWP |            |
|  11 |            PX RECEIVE                              |          |  Q1,01 | SCWP |            |
|  12 |             PX SEND 1 SLAVE                        | :TQ10000 |  Q1,00 | P->S | 1 SLAVE    |
|* 13 |              COUNT STOPKEY                         |          |  Q1,00 | PCWC |            |
|  14 |               PX BLOCK ITERATOR                    |          |  Q1,00 | PCWC |            |
|* 15 |                TABLE ACCESS STORAGE FULL FIRST ROWS| A_O      |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=7)
   4 - filter(ROWNUM<=7)
   6 - filter(ROWNUM<=7)
  10 - filter(ROWNUM<25000000)
  13 - filter(ROWNUM<25000000)

We can see 3 consecutive DFOs now: TQ10000, TQ10001 and TQ10002. But there is a new Operation in LineID:12: PX SEND 1 SLAVE. This indicates the data is sent only to 1 worker in TQ10001. There is also no 20.000 TFOs anymore. The BUFFER SORT in LineID:9 is kind of a NOOP - there is nothing to SORT or BUFFER, still it's a requirement in this type of parallel plan pieces.

The IN-OUT now shows SCWP to indicate the difference.

SQL Monitor also show these changes:

But you have to know what to look for. Only the PX SEND 1 SLAVE indicates the special treatment of  that DFO. 
In plan_table.OTHER_TAG its SINGLE_COMBINED_WITH_PARENT.

In SQL Monitors Parallel-tab it can look as if there is skew in Parallel Server activity; which is technically true but in this case it's on purpose. Don't start corrective actions in this case!

It might look as if there is no reason for this improvement: Only one process is working for this plan lines anyhow. Still, with Single Server DFO, the additional resources of the parallel worker (e.g. dedicated PGA) can be used and the QC is free for other tasks in the execution. 

Of course there is also an underscore-parameter to control this behavior individually. This feature is enabled by default but requires Back to Parallel.

Back to Parallel

Recently I stumbled across a feature which exists for quite some time already. It was implemented in 12.1.

The plan showed a parallel execution which somewhere in the middle had a serializing execution. But later on there were obvious other parallel activity. 

That made me research - and then generate a testcase.

I have generated a table A_O as SELECT * FROM ALL_OBJECTS - just to get some data and keep the plan simple. 

First the SQL as it was executed pre 12.1

select -- BX_08
       /*+ OPT_PARAM('parallel_degree_policy' 'limited') parallel(4) GATHER_PLAN_STATISTICS monitor */ 
       OBJECT_TYPE, count(*) c
from A_O
where rownum < 25000000 
group by OBJECT_TYPE
fetch first 7 rows only; 
select * from dbms_xplan.display_cursor( format =>'ALLSTATS LAST +PARALLEL');

with a plan

Plan hash value: 2817860580
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name     | E-Rows |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |          |        |        |      |            |
|*  1 |  COUNT STOPKEY                               |          |        |        |      |            |
|   2 |   VIEW                                       |          |      7 |        |      |            |
|*  3 |    SORT GROUP BY STOPKEY                     |          |      7 |        |      |            |
|*  4 |     COUNT STOPKEY                            |          |        |        |      |            |
|   5 |      PX COORDINATOR                          |          |        |        |      |            |
|   6 |       PX SEND QC (RANDOM)                    | :TQ10000 |      7 |  Q1,00 | P->S | QC (RAND)  |
|*  7 |        COUNT STOPKEY                         |          |        |  Q1,00 | PCWC |            |
|   8 |         PX BLOCK ITERATOR                    |          |      7 |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS STORAGE FULL FIRST ROWS| A_O      |      7 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=7)
   3 - filter(ROWNUM<=7)
   4 - filter(ROWNUM<25000000)
   7 - filter(ROWNUM<25000000)
   
 
Note
-----
   - Degree of Parallelism is 4 because of hint

We can see in lineID:6 the P->S clearly shows: all data is delivered to the query coordinator. This does the filter for  rownum < 25000000 (this must be in serial mode), and afterwards does the GROUP BY in LineID:3 ← this could be executed in parallel again, but as the QC is already involved, no further parallel executions occur. 

---

This all changes with 12.1: 

Plan hash value: 3726916758
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |          |        |      |            |
|*  1 |  COUNT STOPKEY                                    |          |        |      |            |
|   2 |   PX COORDINATOR                                  |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)                            | :TQ20001 |  Q2,01 | P->S | QC (RAND)  |
|*  4 |     COUNT STOPKEY                                 |          |  Q2,01 | PCWC |            |
|   5 |      VIEW                                         |          |  Q2,01 | PCWP |            |
|*  6 |       SORT GROUP BY STOPKEY                       |          |  Q2,01 | PCWP |            |
|   7 |        PX RECEIVE                                 |          |  Q2,01 | PCWP |            |
|   8 |         PX SEND HASH                              | :TQ20000 |        | S->P | HASH       |
|*  9 |          COUNT STOPKEY                            |          |        |      |            |
|  10 |           PX COORDINATOR                          |          |        |      |            |
|  11 |            PX SEND QC (RANDOM)                    | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|* 12 |             COUNT STOPKEY                         |          |  Q1,00 | PCWC |            |
|  13 |              PX BLOCK ITERATOR                    |          |  Q1,00 | PCWC |            |
|* 14 |               TABLE ACCESS STORAGE FULL FIRST ROWS| A_O      |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=7)
   4 - filter(ROWNUM<=7)
   6 - filter(ROWNUM<=7)
   9 - filter(ROWNUM<25000000)
  12 - filter(ROWNUM<25000000)

Here we see the serial execution between lineID:10 and lineID:8, but the GROUP BY is done in TQ20002 again. You can see the top 2 TQs are 20.000+ whereas the bottom one is 10.000. 

Also the IN-OUT (P->S and S->P) column shows quite fine the switch from parallel execution to serial one. In plan_table.OTHER_TAG its PARALLEL_TO_SERIAL and  PARALLEL_FROM_SERIAL.

SQL Monitor Report reflects this behavior properly. 

This feature is enabled by default (and it's cost is calculated) with parallel_degree_policy=auto. 

Of course there is also an underscore-parameter to control this behavior individually.