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@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Nov 2015 23:58:27 +0100
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
Other related posts:
