Re: PQ - Can a set of slave processes be prevented from starting a rowrource scan if their result is bound to be discarded?

From: Jure Bratina <jure.bratina_at_gmail.com>
Date: Wed, 25 Nov 2015 23:58:27 +0100
Message-ID: <CAC08BH+r7QrTJ9HohTEzq4Won-o8HbOKdu+=7W7m-CUBby2UaQ_at_mail.gmail.com>

>
>
> > Yes, that's it - though I haven't checked it in the most recent versions
> of Oracle.
>

Just for the sake of completeness, I checked the behaviour on 12.1.0.2 (Exadata) and it seems that if a HASH distribution is used, the scan of the second rowsource is still performed, as in older versions:

SQL> select /*+

  2          parallel(t1 2)
  3          parallel(t4 2)
  4          leading(t1 t4)
  5          pq_distribute(t4 hash hash)
  6          no_px_join_filter(t4)
  7          monitor
  8          gather_plan_statistics
  9       */
 10       t1.*

 11 from t1, t4
 12 where t1.id = t4.id1
 13 and t1.small_vc = 'X';

no rows selected

Plan hash value: 3375958929

---

| Id | Operation | Name | Starts | E-Rows |
TQ |IN-OUT| PQ Distrib |

---

| 0 | SELECT STATEMENT | | 1 |
| | | |
| 1 | PX COORDINATOR | | 1 |
| | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 2450 |

Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED            |          |      0 |   2450 |
Q1,02 | PCWP |            |


|   4 |     PX RECEIVE                   |          |      0 |      1 |

Q1,02 | PCWP |            |


|   5 |      PX SEND HYBRID HASH         | :TQ10000 |      0 |      1 |

Q1,00 | P->P | HYBRID HASH|


|   6 |       STATISTICS COLLECTOR       |          |      0 |        |

Q1,00 | PCWC |            |


|   7 |        PX BLOCK ITERATOR         |          |      0 |      1 |

Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS STORAGE FULL| T1       |      0 |      1 |
Q1,00 | PCWP |            |


|   9 |     PX RECEIVE                   |          |      0 |    343K|

Q1,02 | PCWP |            |


|  10 |      PX SEND HYBRID HASH         | :TQ10001 |      0 |    343K|

Q1,01 | P->P | HYBRID HASH|


|  11 |       PX BLOCK ITERATOR          |          |      0 |    343K|

Q1,01 | PCWC |            |
|* 12 |        TABLE ACCESS STORAGE FULL | T4       |      0 |    343K|
Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."ID"="T4"."ID1")
   8 - storage(:Z>=:Z AND :Z<=:Z AND "T1"."SMALL_VC"='X')
       filter("T1"."SMALL_VC"='X')

  12 - storage(:Z>=:Z AND :Z<=:Z)

v$pq_tqstat:

DFO_NUMBER TQ_ID SERVER_TYP PROCES NUM_ROWS BYTES WAITS TIMEOUTS
---------- ---------- ---------- ------ ---------- ---------- ----------

---

         1          0 Producer   P003            0         48
0          0
                                 P002            0         48
0          0

                      Consumer   P000            0         48
396        393
                                 P001            0         48
397        394

                    1 Producer   P003       175104     701472
24          0
                                 P002       167896     672592
24          0

                      Consumer   P000       171500     687032
398        394
                                 P001       171500     687032
400        395

                    2 Producer   P001            0         24
0          0
                                 P000            0         24
0          0

                      Consumer   QC              0         48
2          1

Regards

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 25 2015 - 23:58:27 CET