Re: Partition way join

<maks71_at_gmail.com> wrote in message
news:8ba82692-04f5-44a9-ab75-d893eea2cc2d_at_d4g2000prg.googlegroups.com...
> Can someone inspect the following SQL and tell me if Partition way
> join is occurring here.. I suspect that its not occuring ( As I don't
> see PX PARTITION HASH anywhere is the plan , per 10.2 doc ) but not
> sure. If its not occuring , can some one explain why? All the tables
> are equi-partitioned on same columns.. I'm running 10.2.0.3.
> I aplogize for output is not being in nice format .. I don't know how
> to post that in a formatted way.. IF someone know, please let me know.
>
> Thanks in advance for your answers.
>
> CREATE TABLE temp1 NOLOGGING PARALLEL(DEGREE 6) AS SELECT DISTINCT
> CM_COPY.CM_DRIVER.TRGT_KEY FROM ((CM_COPY.CM_DRIVER INNER JOIN
> CM_COPY.CM_SUBSCRIPTION ON
> (CM_COPY.CM_DRIVER.CUST_ACCT_KEY =
> CM_COPY.CM_SUBSCRIPTION.CUST_ACCT_KEY AND
> CM_COPY.CM_DRIVER.ORG_PART_KEY =
> CM_COPY.CM_SUBSCRIPTION.ORG_PART_KEY)) INNER JOIN
> CM_COPY.V_CM_SERVICE_CODE ON (CM_COPY.CM_SUBSCRIPTION.SRVC_CD_KEY =
> CM_COPY.V_CM_SERVICE_CODE.SRVC_CD_KEY AND
> CM_COPY.CM_SUBSCRIPTION.ORG_PART_KEY =
> CM_COPY.V_CM_SERVICE_CODE.ORG_PART_KEY)) WHERE
> (CM_COPY.V_CM_SERVICE_CODE.SRVC_CD_KEY > 0)
>
> Plan hash value: 1934355852
>
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> | Id | Operation |
> Name | Rows | Bytes |TempSpc| Cost (%CPU)|
> Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> | 0 | CREATE TABLE STATEMENT
> | | | | |
> 165K(100)| | | | | | |
> | 1 | PX COORDINATOR
> | | | | |
> | | | | | | |
> | 2 | PX SEND QC (RANDOM)
> | :TQ10004 | 1265K| 84M| | 165K (2)|
> 00:38:32 | | | Q1,04 | P->S | QC (RAND) |
> | 3 | LOAD AS SELECT
> | | | | |
> | | | | Q1,04 | PCWP | |
> | 4 | HASH UNIQUE
> | | 1265K| 84M| 193M| 165K (2)|
> 00:38:32 | | | Q1,04 | PCWP | |
> | 5 | PX RECEIVE
> | | 1265K| 84M| | 165K (2)|
> 00:38:32 | | | Q1,04 | PCWP | |
> | 6 | PX SEND HASH
> | :TQ10003 | 1265K| 84M| | 165K (2)|
> 00:38:32 | | | Q1,03 | P->P | HASH |
> |* 7 | HASH JOIN
> | | 1265K| 84M| | 165K (2)|
> 00:38:32 | | | Q1,03 | PCWP | |
> | 8 | PX RECEIVE
> | | 2733K| 130M| | 158K (2)|
> 00:37:02 | | | Q1,03 | PCWP | |
> | 9 | PX SEND BROADCAST LOCAL
> | :TQ10002 | 2733K| 130M| | 158K (2)|
> 00:37:02 | | | Q1,02 | P->P | BCST LOCAL |
> |* 10 | HASH JOIN
> | | 2733K| 130M| | 158K (2)|
> 00:37:02 | | | Q1,02 | PCWP | |
> | 11 | PX RECEIVE
> | | 64750 | 2086K| | 1297 (1)|
> 00:00:19 | | | Q1,02 | PCWP | |
> | 12 | PX SEND BROADCAST LOCAL
> | :TQ10001 | 64750 | 2086K| | 1297 (1)|
> 00:00:19 | | | Q1,01 | P->P | BCST LOCAL |
> |* 13 | HASH JOIN RIGHT OUTER
> | | 64750 | 2086K| | 1297 (1)|
> 00:00:19 | | | Q1,01 | PCWP | |
> | 14 | PX RECEIVE
> | | 2635 | 13175 | | 2 (0)|
> 00:00:01 | | | Q1,01 | PCWP | |
> | 15 | PX SEND BROADCAST
> | :TQ10000 | 2635 | 13175 | | 2 (0)|
> 00:00:01 | | | Q1,00 | P->P | BROADCAST |
> | 16 | PX BLOCK ITERATOR
> | | 2635 | 13175 | | 2 (0)|
> 00:00:01 | | | Q1,00 | PCWC | |
> |* 17 | INDEX FAST FULL SCAN |
> I_CM_HIERARCHY_STAND_CD_PK1 | 2635 | 13175 | | 2 (0)|
> 00:00:01 | | | Q1,00 | PCWP | |
> | 18 | PX PARTITION LIST ALL
> | | 64750 | 1770K| | 1295 (1)|
> 00:00:19 | 1 | 33 | Q1,01 | PCWC | |
> |* 19 | TABLE ACCESS BY LOCAL INDEX ROWID|
> CM_STANDARD_CODE_DENORM | 64750 | 1770K| | 1295 (1)|
> 00:00:19 | 1 | 33 | Q1,01 | PCWP | |
> |* 20 | INDEX RANGE SCAN |
> I_CM_STANDARD_CODE_DENORM_IE2 | 64854 | | | 35 (0)| 00:00:01
> | 1 | 33 | Q1,01 | PCWP | |
> | 21 | PX BLOCK ITERATOR
> | | 289M| 4696M| | 156K (1)|
> 00:36:38 | 1 | 33 | Q1,02 | PCWC | |
> |* 22 | TABLE ACCESS FULL |
> CM_SUBSCRIPTION | 289M| 4696M| | 156K (1)|
> 00:36:38 | 1 | 33 | Q1,02 | PCWP | |
> | 23 | PX BLOCK ITERATOR
> | | 27M| 517M| | 6387 (3)|
> 00:01:30 | 1 | 33 | Q1,03 | PCWC | |
> |* 24 | TABLE ACCESS FULL |
> CM_DRIVER | 27M| 517M| | 6387 (3)|
> 00:01:30 | 1 | 33 | Q1,03 | PCWP | |
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 7 -
> access("CM_DRIVER"."ORG_PART_KEY"="CM_SUBSCRIPTION"."ORG_PART_KEY" AND
> "CM_DRIVER"."CUST_ACCT_KEY"="CM_SUBSCRIPTION"."CUST_ACCT_KEY")
> 10 - access("CM_SUBSCRIPTION"."ORG_PART_KEY"="SC"."ORG_PART_KEY" AND
> "CM_SUBSCRIPTION"."SRVC_CD_KEY"="SC"."CD_KEY")
> 13 - access("SC"."STD_CD_KEY"="HI"."STD_CD_KEY")
> 17 - access(:Z>=:Z AND :Z<=:Z)
> 19 - filter("SC"."CD_KEY">0)
> 20 - access("SC"."CD_TYP_CD"='SERVICE')
> 22 - access(:Z>=:Z AND :Z<=:Z)
> filter("CM_SUBSCRIPTION"."SRVC_CD_KEY">0)
> 24 - access(:Z>=:Z AND :Z<=:Z)

The plan is not showing partition-wise joins. Lines 9, 12, and 15 show 'PX SEND BROADCAST'. This means the optimizer has decided that three of your tables are "small" and can be hashed completely in memory by every single PX slave.

This can be incredibly efficient, but can happen in unsuitable cases if the number of partitions involved is smaller than the degree of parallelism used.

If you want to stop this happening, you can use the pq_distribute() hint - it's in the manuals - to control the way the tables are distributed.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html