Hi there,
I know that in parallel execution, BROADCAST distribution means that when scanning a table, each slave from the PX Server Set in charge of reading the T1 table will send every relevent rows to all slaves in the second PX Server set.
As a result, the number of actual rows for the PX SEND BROADCAST operation in the plan will be N times the number of rows of the previous table (or rowsource), N corresponding to the Degree Of Parallelism used for this query.
In the follwoing example, you will notice that the number of rows for each PX SEND BROADCAST operations in the plan will correspond to 16 times the numer of rows of the previous rowsource.
For example, in the operation 31 the HASH JOIN is returning 851K rows and the actual number of rows for the oepration 30 is 14M (851*16 = 13616 = 14M).
Global Information
------------------------------
Status : EXECUTING
Instance ID : 5
Session : TPS_DWH$OWNER (1517:46771)
SQL ID : dnxg2b6gwbvnf
SQL Execution ID : 83886080
Execution Started : 12/02/2019 15:11:39
First Refresh Time : 12/02/2019 15:11:39
Last Refresh Time : 12/02/2019 15:19:07
Duration : 459s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus.exe
Global Stats
=========================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
=========================================================================================================================
| 5509 | 4403 | 1106 | 0.00 | 0.00 | 0.13 | 14M | 476K | 152GB | 358K | 53GB | -21.95% |
=========================================================================================================================
Parallel Execution Details (DOP=16 , Servers Allocated=32)
===========================================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Buffer | Read | Read | Write | Write | Cell | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload | (sample #) |
===========================================================================================================================================================================================
| PX Coordinator | QC | | 0.20 | 0.20 | | 0.00 | | | 2017 | | . | | . | NaN% | |
| p000 | Set 1 | 1 | 283 | 218 | 64 | | | 0.01 | 467K | 26905 | 7GB | 23440 | 3GB | -56.25% | direct path read temp (45) |
| | | | | | | | | | | | | | | | direct path write temp (18) |
| p001 | Set 1 | 2 | 284 | 208 | 76 | | 0.00 | 0.00 | 528K | 26436 | 7GB | 22377 | 3GB | -47.06% | PX Deq: Table Q Normal (1) |
| | | | | | | | | | | | | | | | cell smart table scan (2) |
| | | | | | | | | | | | | | | | direct path read temp (52) |
| | | | | | | | | | | | | | | | direct path write temp (25) |
| p002 | Set 1 | 3 | 278 | 202 | 76 | | 0.00 | 0.00 | 554K | 26474 | 7GB | 22263 | 3GB | -42.86% | direct path read temp (52) |
| | | | | | | | | | | | | | | | direct path write temp (24) |
| p003 | Set 1 | 4 | 289 | 225 | 64 | | | 0.01 | 526K | 26451 | 7GB | 23535 | 3GB | -51.52% | cell smart table scan (2) |
| | | | | | | | | | | | | | | | direct path read temp (37) |
| | | | | | | | | | | | | | | | direct path write temp (24) |
| p004 | Set 1 | 5 | 279 | 213 | 66 | | 0.00 | 0.00 | 524K | 27018 | 7GB | 22805 | 3GB | -47.06% | direct path read temp (48) |
| | | | | | | | | | | | | | | | direct path write temp (17) |
| p005 | Set 1 | 6 | 278 | 219 | 59 | | | 0.00 | 536K | 26283 | 7GB | 22607 | 3GB | -47.06% | cell smart table scan (1) |
| | | | | | | | | | | | | | | | direct path read temp (45) |
| | | | | | | | | | | | | | | | direct path write temp (17) |
| p006 | Set 1 | 7 | 203 | 151 | 52 | | | 0.00 | 628K | 24411 | 7GB | 19592 | 3GB | -23.46% | direct path read temp (36) |
| | | | | | | | | | | | | | | | direct path write temp (16) |
| p007 | Set 1 | 8 | 315 | 244 | 72 | | | 0.03 | 524K | 28586 | 7GB | 23706 | 3GB | -51.52% | cell smart table scan (3) |
| | | | | | | | | | | | | | | | direct path read temp (57) |
| | | | | | | | | | | | | | | | direct path write temp (17) |
| p008 | Set 1 | 9 | 283 | 206 | 77 | | | 0.00 | 382K | 23061 | 5GB | 20526 | 3GB | -61.29% | direct path read temp (57) |
| | | | | | | | | | | | | | | | direct path write temp (25) |
| p009 | Set 1 | 10 | 283 | 218 | 65 | | | 0.00 | 604K | 28296 | 8GB | 23455 | 3GB | -40.85% | direct path read temp (49) |
| | | | | | | | | | | | | | | | direct path write temp (16) |
| p010 | Set 1 | 11 | 278 | 210 | 67 | | | 0.01 | 745K | 28848 | 9GB | 22670 | 3GB | -26.58% | cell smart table scan (1) |
| | | | | | | | | | | | | | | | direct path read temp (51) |
| | | | | | | | | | | | | | | | direct path write temp (17) |
| p011 | Set 1 | 12 | 276 | 205 | 71 | | | 0.00 | 536K | 26638 | 7GB | 22215 | 3GB | -44.93% | cell smart table scan (1) |
| | | | | | | | | | | | | | | | direct path read temp (53) |
| | | | | | | | | | | | | | | | direct path write temp (19) |
| p012 | Set 1 | 13 | 279 | 203 | 77 | | | 0.00 | 522K | 25815 | 7GB | 21931 | 3GB | -47.06% | cell smart table scan (1) |
| | | | | | | | | | | | | | | | direct path read temp (55) |
| | | | | | | | | | | | | | | | direct path write temp (24) |
| p013 | Set 1 | 14 | 293 | 225 | 68 | | | 0.00 | 664K | 27938 | 8GB | 22527 | 3GB | -33.33% | cell smart table scan (1) |
| | | | | | | | | | | | | | | | direct path read temp (54) |
| | | | | | | | | | | | | | | | direct path write temp (16) |
| p014 | Set 1 | 15 | 278 | 202 | 76 | | | 0.01 | 500K | 25530 | 7GB | 21754 | 3GB | -49.25% | PX Deq: Table Q Normal (1) |
| | | | | | | | | | | | | | | | cell smart table scan (1) |
| | | | | | | | | | | | | | | | direct path read temp (48) |
| | | | | | | | | | | | | | | | direct path write temp (24) |
| p015 | Set 1 | 16 | 277 | 203 | 74 | | | 0.00 | 458K | 25377 | 6GB | 22352 | 3GB | -53.85% | cell smart table scan (2) |
| | | | | | | | | | | | | | | | direct path read temp (51) |
| | | | | | | | | | | | | | | | direct path write temp (26) |
| p016 | Set 2 | 1 | 59 | 59 | 0.22 | | | 0.00 | 291K | 2749 | 2GB | | . | 66.89% | |
| p017 | Set 2 | 2 | 64 | 63 | 0.27 | | | 0.00 | 398K | 3905 | 3GB | | . | 73.33% | |
| p018 | Set 2 | 3 | 68 | 68 | 0.29 | | | 0.00 | 372K | 3589 | 3GB | | . | 70.15% | |
| p019 | Set 2 | 4 | 63 | 62 | 0.28 | | | 0.00 | 323K | 3138 | 2GB | | . | 67.95% | |
| p020 | Set 2 | 5 | 61 | 61 | 0.19 | | | 0.00 | 298K | 2810 | 2GB | | . | 74.94% | |
| p021 | Set 2 | 6 | 68 | 68 | 0.22 | | | 0.00 | 254K | 2580 | 2GB | | . | 54.75% | |
| p022 | Set 2 | 7 | 72 | 72 | 0.23 | | | 0.00 | 254K | 2655 | 2GB | | . | 55.16% | |
| p023 | Set 2 | 8 | 85 | 85 | 0.20 | | | 0.00 | 417K | 3988 | 3GB | | . | 75.00% | |
| p024 | Set 2 | 9 | 60 | 60 | 0.22 | | | 0.00 | 420K | 3944 | 3GB | | . | 75.85% | |
| p025 | Set 2 | 10 | 63 | 62 | 0.23 | | 0.00 | 0.00 | 333K | 3192 | 3GB | | . | 68.94% | |
| p026 | Set 2 | 11 | 62 | 62 | 0.28 | | | 0.00 | 389K | 3712 | 3GB | | . | 73.68% | |
| p027 | Set 2 | 12 | 60 | 60 | 0.24 | | | 0.00 | 226K | 2463 | 2GB | | . | 56.33% | |
| p028 | Set 2 | 13 | 63 | 63 | 0.30 | | | 0.00 | 363K | 3419 | 3GB | | . | 72.68% | |
| p029 | Set 2 | 14 | 76 | 76 | 0.26 | | | 0.00 | 507K | 4656 | 4GB | | . | 83.25% | |
| p030 | Set 2 | 15 | 62 | 62 | 0.23 | | | 0.00 | 237K | 2477 | 2GB | | . | 54.75% | |
| p031 | Set 2 | 16 | 68 | 68 | 0.20 | | | 0.00 | 310K | 3022 | 2GB | | . | 69.33% | |
===========================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3534356073)
==============================================================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Cell | Mem | Temp | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | Offload | | | (%) | (# samples) | |
==============================================================================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 33 | | | | | | | | | | | |
| 1 | PX COORDINATOR | | | | | | 33 | | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10008 | 25985 | 182K | | | 16 | | | | | | | | | | | |
| -> 3 | HASH JOIN | | 25985 | 182K | 158 | +301 | 16 | 0 | 64462 | 15GB | 96430 | 23GB | | 4G | 25G | 33.22 | Cpu (1001) | 40% |
| | | | | | | | | | | | | | | | | | direct path read temp (619) | |
| | | | | | | | | | | | | | | | | | direct path write temp (164) | |
| 4 | PART JOIN FILTER CREATE | :BF0000 | 25985 | 173K | 39 | +301 | 16 | 50M | | | | | | | | 0.41 | Cpu (22) | |
| 5 | PX RECEIVE | | 25985 | 173K | 39 | +301 | 16 | 50M | | | | | | | | 0.67 | Cpu (34) | |
| | | | | | | | | | | | | | | | | | PX Deq: Table Q Normal (2) | |
| 6 | PX SEND BROADCAST | :TQ10007 | 25985 | 173K | 27 | +300 | 16 | 50M | | | | | | | | 0.56 | Cpu (30) | |
| 7 | HASH JOIN | | 25985 | 173K | 98 | +229 | 16 | 3M | | | | | | | | 8.98 | Cpu (482) | |
| 8 | PART JOIN FILTER CREATE | :BF0001 | 25986 | 154K | 36 | +230 | 16 | 50M | | | | | | | | 0.19 | Cpu (10) | |
| 9 | PX RECEIVE | | 25986 | 154K | 37 | +229 | 16 | 50M | | | | | | | | 0.50 | Cpu (27) | |
| 10 | PX SEND BROADCAST | :TQ10006 | 25986 | 154K | 73 | +228 | 16 | 50M | | | | | | | | 0.56 | Cpu (30) | |
| 11 | HASH JOIN | | 25986 | 154K | 216 | +85 | 16 | 3M | 261K | 30GB | 261K | 30GB | | | | 26.07 | Cpu (1068) | 100% |
| | | | | | | | | | | | | | | | | | direct path read temp (171) | |
| | | | | | | | | | | | | | | | | | direct path write temp (161) | |
| 12 | PART JOIN FILTER CREATE | :BF0002 | 26038 | 144K | 44 | +85 | 16 | 50M | | | | | | | | 0.34 | Cpu (18) | |
| 13 | PX RECEIVE | | 26038 | 144K | 44 | +85 | 16 | 50M | | | | | | | | 0.41 | Cpu (22) | |
| 14 | PX SEND BROADCAST | :TQ10005 | 26038 | 144K | 144 | +85 | 16 | 50M | | | | | | | | 0.50 | Cpu (27) | |
| 15 | HASH JOIN | | 26038 | 144K | 188 | +41 | 16 | 3M | | | | | | | | 3.39 | Cpu (182) | |
| 16 | PART JOIN FILTER CREATE | :BF0003 | 26038 | 128K | 45 | +41 | 16 | 50M | | | | | | | | 0.34 | Cpu (18) | |
| 17 | PX RECEIVE | | 26038 | 128K | 44 | +42 | 16 | 50M | | | | | | | | 0.50 | Cpu (27) | |
| 18 | PX SEND BROADCAST | :TQ10004 | 26038 | 128K | 45 | +41 | 16 | 50M | | | | | | | | 0.47 | Cpu (25) | |
| 19 | HASH JOIN | | 26038 | 128K | 58 | +28 | 16 | 3M | | | | | | | | 1.42 | Cpu (76) | |
| 20 | PART JOIN FILTER CREATE | :BF0005 | 26038 | 97862 | 9 | +30 | 16 | 21M | | | | | | | | 0.13 | Cpu (7) | |
| 21 | PX RECEIVE | | 26038 | 97862 | 11 | +28 | 16 | 21M | | | | | | | | 0.19 | Cpu (10) | |
| 22 | PX SEND BROADCAST | :TQ10003 | 26038 | 97862 | 14 | +28 | 16 | 21M | | | | | | | | 0.09 | Cpu (5) | |
| 23 | HASH JOIN | | 26038 | 97862 | 20 | +22 | 16 | 1M | | | | | | | | 0.22 | Cpu (12) | |
| 24 | PART JOIN FILTER CREATE | :BF0007 | 26038 | 61648 | 20 | +22 | 16 | 14M | | | | | | | | 0.09 | Cpu (5) | |
| 25 | PX RECEIVE | | 26038 | 61648 | 19 | +23 | 16 | 14M | | | | | | | | 0.02 | Cpu (1) | |
| 26 | PX SEND BROADCAST | :TQ10002 | 26038 | 61648 | 7 | +22 | 16 | 14M | | | | | | | | 0.06 | Cpu (3) | |
| 27 | HASH JOIN | | 26038 | 61648 | 28 | +1 | 16 | 851K | | | | | | | | 4.51 | Cpu (242) | |
| 28 | PART JOIN FILTER CREATE | :BF0009 | 25858 | 72 | 4 | +1 | 16 | 14M | | | | | | | | 0.07 | Cpu (4) | |
| 29 | PX RECEIVE | | 25858 | 72 | 4 | +1 | 16 | 14M | | | | | | | | 0.04 | Cpu (2) | |
| 30 | PX SEND BROADCAST | :TQ10001 | 25858 | 72 | 21 | +2 | 16 | 14M | | | | | | | | | | |
| 31 | HASH JOIN | | 25858 | 72 | 21 | +2 | 16 | 851K | | | | | | | | | | |
| 32 | PART JOIN FILTER CREATE | :BF0011 | 1 | 15 | 1 | +2 | 16 | 16 | | | | | | | | | | |
| 33 | PX RECEIVE | | 1 | 15 | 1 | +2 | 16 | 16 | | | | | | | | | | |
| 34 | PX SEND BROADCAST | :TQ10000 | 1 | 15 | 1 | +2 | 16 | 16 | | | | | | | | | | |
| 35 | PX BLOCK ITERATOR | | 1 | 15 | 1 | +2 | 16 | 1 | | | | | | | | | | |
| 36 | TABLE ACCESS STORAGE FULL | SNP_SNAPSHOT_TPS | 1 | 15 | 1 | +2 | 208 | 1 | | | | | | | | | | |
| 37 | PX BLOCK ITERATOR | | 25858 | 57 | 21 | +2 | 16 | 851K | | | | | | | | | | |
| 38 | TABLE ACCESS STORAGE FULL | SNP_SNAPSHOT_SK_TPS | 25858 | 57 | 21 | +2 | 208 | 851K | | | | | | | | | | |
| 39 | PX BLOCK ITERATOR | | 554M | 61463 | 27 | +2 | 16 | 463M | | | | | | | | 0.80 | Cpu (43) | |
| 40 | TABLE ACCESS STORAGE FULL | OTC_DAT_TRADE_VERSION | 554M | 61463 | 27 | +2 | 183 | 463M | 42566 | 39GB | | | 83.16% | | | 0.47 | Cpu (24) | |
| | | | | | | | | | | | | | | | | | cell smart table scan (1) | |
| 41 | PX BLOCK ITERATOR | | 760M | 36058 | 14 | +28 | 16 | 4M | | | | | | | | 0.02 | Cpu (1) | |
| 42 | TABLE ACCESS STORAGE FULL | OTC_DAT_PRODUCT_STREAM | 760M | 36058 | 14 | +28 | 186 | 4M | 23310 | 22GB | | | 99.13% | | | | | |
| 43 | PX BLOCK ITERATOR | | 2G | 30071 | 48 | +38 | 16 | 52M | | | | | | | | 0.11 | Cpu (6) | |
| 44 | TABLE ACCESS STORAGE FULL | OTC_DAT_OBSERVABLE | 2G | 30071 | 49 | +37 | 178 | 52M | 19642 | 17GB | | | 89.44% | | | 0.19 | Cpu (7) | 100% |
| | | | | | | | | | | | | | | | | | cell smart table scan (3) | |
| 45 | PX BLOCK ITERATOR | | 2G | 15790 | 158 | +71 | 16 | 162M | | | | | | | | 0.22 | Cpu (12) | |
| 46 | TABLE ACCESS STORAGE FULL | OTC_DAT_OBS_FIXING | 2G | 15790 | 158 | +71 | 182 | 162M | 11221 | 9GB | | | 83.25% | | | 0.26 | Cpu (14) | 100% |
| 47 | PX BLOCK ITERATOR | | 2G | 9520 | 121 | +133 | 16 | 2G | | | | | | | | 3.37 | Cpu (181) | |
| 48 | TABLE ACCESS STORAGE FULL | OTC_DAT_OBS_REF_STYPE_CODE | 2G | 9520 | 131 | +123 | 184 | 2G | 18426 | 5GB | | | -143.90% | | | 2.29 | Cpu (113) | 100% |
| | | | | | | | | | | | | | | | | | cell smart table scan (10) | |
| 49 | PX BLOCK ITERATOR | | 778M | 17959 | 62 | +265 | 16 | 805M | | | | | | | | 1.53 | Cpu (82) | |
| 50 | TABLE ACCESS STORAGE FULL | OTC_DAT_STREAM_UNDERLYING | 778M | 17959 | 62 | +265 | 188 | 805M | 17768 | 11GB | | | -1.01% | | | 0.78 | Cpu (42) | 100% |
| -> 51 | PX BLOCK ITERATOR | | 2G | 9526 | 101 | +358 | 16 | 2G | | | | | | | | 3.46 | Cpu (186) | |
| -> 52 | TABLE ACCESS STORAGE FULL | OTC_DAT_OBS_LEVEL | 2G | 9526 | 125 | +334 | 187 | 2G | 18163 | 5GB | | | -156.41% | 15M | | 2.57 | Cpu (137) | 100% |
| | | | | | | | | | | | | | | | | | cell smart table scan (1) | |
==============================================================================================================================================================================================================================================================================
But look at the following plan involving BROADCAST LOCAL operations instead of BROADCAST. This time the number of rows sent by the first PX Server set is not multiplied by 16 but remains the same.
Global Information
------------------------------
Status : EXECUTING
Instance ID : 5
Session : TPS_DWH$OWNER (2872:10163)
SQL ID : 96v4jzb1zyb2j
SQL Execution ID : 83886080
Execution Started : 12/02/2019 14:59:30
First Refresh Time : 12/02/2019 14:59:30
Last Refresh Time : 12/02/2019 15:08:13
Duration : 524s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus.exe
Fetch Calls : 4309
Global Stats
===============================================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
============================================