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.