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!
In plan_table.OTHER_TAG its SINGLE_COMBINED_WITH_PARENT.
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.
