Re: Partition way join

From: <maks71_at_gmail.com>
Date: Sun, 3 Feb 2008 14:56:09 -0800 (PST)
Message-ID: <463674d0-c8ff-42ef-b4e2-eba5c6abd4c0@e23g2000prf.googlegroups.com>


On Feb 2, 6:40am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <mak..._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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide quoted text -
>
> - Show quoted text -

Thank you Jonathan for the answer. a few f/wup questions.. (Sorry its little longer... )

What should one be looking to confirm if partition-wise join occurring or not..

  1. When no parallelism used
  2. When parallelism used

Oracle Doc. says, look for presence of "PX PARTITION <Partition Type> ALL" above the actual Hash join...not clear on what to look for when not using parallelism.

In following example,[FYI, I have removed the ref. to view from origional posted SQL for clarity ]. CM_DRIVER and CM_SUBSCRIPTION tables are LIST partitioned on ORA_PART_KEY and joined on same column with one additional column CUST_ACCT_KEY.

Size of CM_DRIVER is 1.6GB total w/ partition size ranging from 4MB-240MB.
Size of CM_SUBSCRIPTION is 40GB w/ partition size ranging from 64MB to 4 GB.
Total # of partitions are 33.

 Explain plan shown with PARALLEL & NOPARALLEL clause.

I think, w/o PARALLEL Clause is using Partition-wise(P-W) join (of course in serial mode) as indicated by Line#3 "PARTITION LIST ALL" above HASH JOIN with respective parition range in PSTART & PSTOP column. Is my interpretation correct?

However, I don't see any such indication in explain plan of one with the PARALLEL clause... Per my understanding (from 10g doc ) , it's not using P-W join and not sure why its not using one.. Am I interpreting this correctly? If so, Can you shed some lights as to why its not using P-W join....

Also, its not showing correct sizes of the table in explain plan. Showing 3GB(instead of 40G) for CM_SUBSCRIPTION and 517M (instead of 1.6G) in explain plan though rowcount is almost correct. Below are the stats.

TABLE_Name #ROWS Blocks
--------------- ------------ ------------ CM_SUBSCRIPTION 289,592,090 2,541,839 CM_DRIVER 27,102,910 102,162

  • W/O PARALLEL Clause.

explain plan for
CREATE TABLE UAC_7611_q1
NOLOGGING
 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))); PLAN_TABLE_OUTPUT



Plan hash value: 1075960132
| Id  | Operation                | Name                  | Rows  |
Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
|   0 | CREATE TABLE STATEMENT   |                       |    10M|
296M|       |   966K  (3)| 03:45:34 |       |       |
|   1 |  LOAD AS SELECT          | UAC_7611_Q1           |
|       |       |            |       |  |       |
|   2 |   HASH UNIQUE            |                       |    10M|
296M|  5506M|   863K  (4)| 03:21:29 |       |       |
|   3 |    PARTITION LIST ALL    |                       |   134M|
3965M|       |   477K  (3)| 01:51:21 |     1 |    33 |
|*  4 |     HASH JOIN            |                       |   134M|
3965M|    25M|   477K  (3)| 01:51:21 |       |       |
|   5 |      TABLE ACCESS FULL   | CM_DRIVER             |    27M|
517M|       | 34582   (3)| 00:08:05 |     1 |    33 |
|   6 |      INDEX FAST FULL SCAN| I_CM_SUBSCRIPTION_IE1 |   289M|
3039M|       |   129K  (3)| 00:30:20 |     1 |    33 |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   4 -
access("CM_DRIVER"."ORG_PART_KEY"="CM_SUBSCRIPTION"."ORG_PART_KEY" AND  

"CM_DRIVER"."CUST_ACCT_KEY"="CM_SUBSCRIPTION"."CUST_ACCT_KEY") 19 rows selected.

  • W/ PARALLEL Clause.

explain plan for
CREATE TABLE UAC_7611_q1
NOLOGGING
PARALLEL
 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))); PLAN_TABLE_OUTPUT



Plan hash value: 2459914318
| Id  | Operation                   | Name            | Rows  | Bytes |
TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ
Distrib |
|   0 | CREATE TABLE STATEMENT      |                 |    10M|
296M|       |  3426   (3)| 00:00:48 |       |       |        |
|            |
|   1 |  PX COORDINATOR             |                 |       |
|       |            |          |       |       |        |
|            |
|   2 |   PX SEND QC (RANDOM)       | :TQ10003        |    10M|
296M|       |  3104   (3)| 00:00:44 |       |       |  Q1,03 | P->S |
QC (RAND)  |
|   3 |    LOAD AS SELECT           | UAC_7611_Q1     |       |
|       |            |          |       |       |  Q1,03 | PCWP
|            |
|   4 |     HASH UNIQUE             |                 |    10M|
296M|  5506M|  3104   (3)| 00:00:44 |       |       |  Q1,03 | PCWP
|            |
|   5 |      PX RECEIVE             |                 |   134M|
3965M|       |  3066   (2)| 00:00:43 |       |       |  Q1,03 | PCWP
|            |
|   6 |       PX SEND HASH          | :TQ10002        |   134M|
3965M|       |  3066   (2)| 00:00:43 |       |       |  Q1,02 | P->P |
HASH       |
|*  7 |        HASH JOIN BUFFERED   |                 |   134M|
3965M|       |  3066   (2)| 00:00:43 |       |       |  Q1,02 | PCWP
|            |
|   8 |         PX RECEIVE          |                 |    27M|
517M|       |   120   (3)| 00:00:02 |       |       |  Q1,02 | PCWP
|            |
|   9 |          PX SEND HASH LOCAL | :TQ10000        |    27M|
517M|       |   120   (3)| 00:00:02 |       |       |  Q1,00 | P->P |
HASH LOCAL |
|  10 |           PX BLOCK ITERATOR |                 |    27M|
517M|       |   120   (3)| 00:00:02 |     1 |    33 |  Q1,00 | PCWC
|            |
|  11 |            TABLE ACCESS FULL| CM_DRIVER       |    27M|
517M|       |   120   (3)| 00:00:02 |     1 |    33 |  Q1,00 | PCWP
|            |
|  12 |         PX RECEIVE          |                 |   289M|
3039M|       |  2936   (1)| 00:00:42 |       |       |  Q1,02 | PCWP
|            |
|  13 |          PX SEND HASH LOCAL | :TQ10001        |   289M|
3039M|       |  2936   (1)| 00:00:42 |       |       |  Q1,01 | P->P |
HASH LOCAL |
|  14 |           PX BLOCK ITERATOR |                 |   289M|
3039M|       |  2936   (1)| 00:00:42 |     1 |    33 |  Q1,01 | PCWC
|            |
|  15 |            TABLE ACCESS FULL| CM_SUBSCRIPTION |   289M|
3039M|       |  2936   (1)| 00:00:42 |     1 |    33 |  Q1,01 | 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") Received on Sun Feb 03 2008 - 16:56:09 CST

Original text of this message