【Oracle】并行查询之DFO

2021-09-21 11:58:43

DFO即“Data Flow Operator”，实际上以并行方式执行的并不是查询而是“Data Flow Operator”(DFOs),一个查询可以有一个或者几个DFOs组成。DFO tree 有DFOs组成，通常一个查询有一个DFO tree

比如：

select /*+ parallel(t1 4) parallel(t2 4) */ rownum, t1.id from t1, t2 where t1.id = t2.id;

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

| 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 | |

------------------------------------------------------------------------------------------------------------------

查看v$pq_tqstat

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

从上面的结果看,有一个DFOtree，三个TQ(table queue)，并且p000,p001,p002 不仅仅是消费者进程同时也是生产者进程。

当然也有很多sql 是多DFO trees ,使用如下sql：

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;

执行计划：

-------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------

| 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 | |

-------------------------------------------------------------------------------------------------------------------------

查看v$pq_tqstat

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

上面的sql 有三个DFO trees

本文翻译自 http://os2ora.com/en/how-to-have-multiple-dfo-trees/

码农公寓

相关文章