Re: Partition way join
Date: Sun, 3 Feb 2008 14:56:09 -0800 (PST)
Message-ID: <463674d0-c8ff-42ef-b4e2-eba5c6abd4c0@e23g2000prf.googlegroups.com>
On Feb 2, 6:40 am, "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..
- When no parallelism used
- 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| PQDistrib |
| 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