How to have multiple DFO trees
DFO means "Data Flow Operator". Actually, “queries” don’t run in parallel, it’s "data flow operations" (DFOs) that run in parallel, and a single query can be made up of several data flow operations. DFO tree is composite with DFOs, usually one query have one DFO tree. such as
select /*+ parallel(t1 4) parallel(t2 4) */ rownum, t1.id from t1, t2 where t1.id = t2.id; ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 100 | 600 | 5 (20)| 00:00:01 | | | | | 1 | COUNT | | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10002 | 100 | 600 | 5 (20)| 00:00:01 | Q1,02 | P->S | QC (RAND) | |* 4 | HASH JOIN BUFFERED | | 100 | 600 | 5 (20)| 00:00:01 | Q1,02 | PCWP | | | 5 | PX RECEIVE | | 100 | 300 | 2 (0)| 00:00:01 | Q1,02 | PCWP | | | 6 | PX SEND HASH | :TQ10000 | 100 | 300 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH | | 7 | PX BLOCK ITERATOR | | 100 | 300 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 8 | TABLE ACCESS FULL| T1 | 100 | 300 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | | 9 | PX RECEIVE | | 100 | 300 | 2 (0)| 00:00:01 | Q1,02 | PCWP | | | 10 | PX SEND HASH | :TQ10001 | 100 | 300 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH | | 11 | PX BLOCK ITERATOR | | 100 | 300 | 2 (0)| 00:00:01 | Q1,01 | PCWC | | | 12 | TABLE ACCESS FULL| T2 | 100 | 300 | 2 (0)| 00:00:01 | Q1,01 | PCWP | | ------------------------------------------------------------------------------------------------------------------
The result from v$pq_tqstat:
hlr@ORCL> select * from v$pq_tqstat order by dfo_number , tq_id , server_type;
DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES OPEN_TIME AVG_LATENCY WAITS TIMEOUTS PROCESS INSTANCE
---------- ---------- --------------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------
1 0 Consumer 28 192 0 0 10 1 P002 1
1 0 Consumer 26 184 0 0 9 1 P001 1
1 0 Consumer 19 156 0 0 10 2 P000 1
1 0 Consumer 27 188 0 0 10 3 P003 1
1 0 Producer 0 80 0 0 0 0 P007 1
1 0 Producer 0 80 0 0 0 0 P005 1
1 0 Producer 100 480 0 0 2 1 P004 1
1 0 Producer 0 80 0 0 0 0 P006 1
1 1 Consumer 28 192 0 0 10 1 P002 1
1 1 Consumer 26 184 0 0 9 1 P001 1
1 1 Consumer 19 156 0 0 10 3 P000 1
1 1 Consumer 27 188 0 0 10 2 P003 1
1 1 Producer 100 480 0 0 1 0 P004 1
1 1 Producer 0 80 0 0 0 0 P007 1
1 1 Producer 0 80 0 0 0 0 P005 1
1 1 Producer 0 80 0 0 0 0 P006 1
1 2 Consumer 100 480 0 0 0 0 QC 1
1 2 Producer 27 128 0 0 0 0 P003 1
1 2 Producer 26 124 0 0 0 0 P001 1
1 2 Producer 28 132 0 0 0 0 P002 1
1 2 Producer 19 96 0 0 0 0 P000 1
So, there is on DFO tree, three TQ(table queue), and P000,P001,P002 not only acted as Consumer, but also Producer. The DFO tree can be visualized(the rectangle stands for table queue, and ellipse stands for servers/slaves :
There are quite a lot of queries that have multiple DFO trees, just a demonstration innovated from Jonathan’s blog
select /*+ parallel(t1 4) parallel(v1 8 ) */ t1.id from t1, (select /*+ parallel(t1 4) parallel(t2 4) */ rownum, t1.id from t1, t2 where t1.id = t2.id) v1 where t1.id = v1.id;
Execution plan:
------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 600 | 7 (15)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ20002 | 100 | 600 | 7 (15)| 00:00:01 | Q2,02 | P->S | QC (RAND) | |* 3 | HASH JOIN | | 100 | 600 | 7 (15)| 00:00:01 | Q2,02 | PCWP | | | 4 | PX RECEIVE | | 100 | 300 | 2 (0)| 00:00:01 | Q2,02 | PCWP | | | 5 | PX SEND HASH | :TQ20001 | 100 | 300 | 2 (0)| 00:00:01 | Q2,01 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 100 | 300 | 2 (0)| 00:00:01 | Q2,01 | PCWC | | | 7 | TABLE ACCESS FULL | T1 | 100 | 300 | 2 (0)| 00:00:01 | Q2,01 | PCWP | | | 8 | BUFFER SORT | | | | | | Q2,02 | PCWC | | | 9 | PX RECEIVE | | 100 | 300 | 5 (20)| 00:00:01 | Q2,02 | PCWP | | | 10 | PX SEND HASH | :TQ20000 | 100 | 300 | 5 (20)| 00:00:01 | | S->P | HASH | | 11 | VIEW | | 100 | 300 | 5 (20)| 00:00:01 | | | | | 12 | COUNT | | | | | | | | | | 13 | PX COORDINATOR | | | | | | | | | | 14 | PX SEND QC (RANDOM) | :TQ10002 | 100 | 600 | 5 (20)| 00:00:01 | Q1,02 | P->S | QC (RAND) | |* 15 | HASH JOIN BUFFERED | | 100 | 600 | 5 (20)| 00:00:01 | Q1,02 | PCWP | | | 16 | PX RECEIVE | | 100 | 300 | 2 (0)| 00:00:01 | Q1,02 | PCWP | | | 17 | PX SEND HASH | :TQ10000 | 100 | 300 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH | | 18 | PX BLOCK ITERATOR | | 100 | 300 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 19 | TABLE ACCESS FULL| T1 | 100 | 300 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | | 20 | PX RECEIVE | | 100 | 300 | 2 (0)| 00:00:01 | Q1,02 | PCWP | | | 21 | PX SEND HASH | :TQ10001 | 100 | 300 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH | | 22 | PX BLOCK ITERATOR | | 100 | 300 | 2 (0)| 00:00:01 | Q1,01 | PCWC | | | 23 | TABLE ACCESS FULL| T2 | 100 | 300 | 2 (0)| 00:00:01 | Q1,01 | PCWP | | -------------------------------------------------------------------------------------------------------------------------
Information from v$pq_tqstat;
hlr@ORCL> select * from v$pq_tqstat order by dfo_number , tq_id , server_type;
DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES OPEN_TIME AVG_LATENCY WAITS TIMEOUTS PROCESS INSTANCE
---------- ---------- --------------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------
1 0 Consumer 27 128 0 0 14 8 P003 1
1 0 Consumer 19 96 0 0 12 6 P000 1
1 0 Consumer 26 124 0 0 12 6 P001 1
1 0 Consumer 28 132 0 0 14 8 P002 1
1 1 Consumer 27 188 0 0 13 7 P003 1
1 1 Consumer 26 184 0 0 14 9 P001 1
1 1 Consumer 28 192 0 0 11 6 P002 1
1 1 Consumer 19 156 0 0 14 9 P000 1
1 1 Producer 0 80 0 0 0 0 P006 1
1 1 Producer 100 480 0 0 1 0 P004 1
1 1 Producer 0 80 0 0 0 0 P005 1
1 1 Producer 0 80 0 0 0 0 P007 1
1 2 Producer 26 124 0 0 0 0 P001 1
1 2 Producer 19 96 0 0 0 0 P000 1
1 2 Producer 28 132 0 0 0 0 P002 1
1 2 Producer 27 128 0 0 0 0 P003 1
2 0 Consumer 26 184 0 0 14 7 P009 1
2 0 Consumer 19 156 0 0 15 8 P008 1
2 0 Consumer 28 192 0 0 13 7 P010 1
2 0 Consumer 27 188 0 0 13 8 P011 1
2 0 Producer 100 480 0 0 2 1 P016 1
2 0 Producer 0 80 0 0 0 0 P013 1
2 0 Producer 100 480 0 0 4294967242 4294967282 QC 1
2 0 Producer 0 80 0 0 0 0 P014 1
2 0 Producer 0 80 0 0 0 0 P012 1
2 1 Consumer 19 156 0 0 14 8 P008 1
2 1 Consumer 27 188 0 0 14 8 P011 1
2 1 Consumer 28 192 0 0 16 7 P010 1
2 1 Consumer 26 184 0 0 13 7 P009 1
2 1 Producer 100 480 0 0 1 0 P016 1
2 1 Producer 0 80 0 0 0 0 P014 1
2 1 Producer 0 80 0 0 0 0 P012 1
2 1 Producer 0 80 0 0 0 0 P013 1
2 2 Consumer 100 480 0 0 2 1 QC 1
2 2 Consumer 100 480 0 0 4 2 QC 1
2 2 Producer 28 132 0 0 0 0 P010 1
2 2 Producer 27 128 0 0 0 0 P011 1
2 2 Producer 19 96 0 0 0 0 P008 1
2 2 Producer 26 124 0 0 0 0 P009 1
SO, there are two DFO trees. Also, the DFO trees can be visualized:
Questions is: there are two QC in TQ(2,2), which have different waits and timeouts in v$pq_tqstat. And the TQ(1,0) have no producers in the v$pq_tqstat, I just add it for completion.