how to tell if you are getting a partition wise join

From: <ryan_gaffuri_at_comcast.net>
Date: Wed, 19 Jul 2006 20:27:36 +0000
Message-Id: <071920062027.24002.44BE95B7000F305E00005DC22200735446079D9A00000E09A1020E979D@comcast.net>

I am doing some simple tests. I made 2 copies of dba_objects that are hash partitioned with 8 partitions. called hash_test_a and hash_test_c I added a small attachment with the two plans. I want to make sure it formats properly.

I ran this query:

explain plan for

    select /*+ parallel (a,8) parallel (b,8) */ count(*)     from hash_test_a a, hash_test_b b
   where a.object_id = b.object_id

My expectation before running this query was:

1. Each slave will scan a partition
2. Then eight slaves will each do a partition wise hash join.
3. The results will queued. The query coordinator will dequeue the results. 

Is this a correct interpretation? I am not sure if the plan says that.

This is the second plan:

   explain plan for

       select /*+ parallel (a,8) parallel (b,8) */ count(*)
       from hash_test_a partition(HASH_TEST_A01) a, hash_test_c partition(HASH_TEST_A01) b
      where a.object_id = b.object_id

This is the same query accept it is restricted to just the first partition. The plan changes considerably. This is what I find interesting:  PX RECEIVE
    PX SEND HASH
        PX BLOCK ITERATOR This happens before each table scan. This tells me(if I am reading it correctly) that 1 parallel slave is reading a partition and then queing it up for the query coordinator. However, how come I don't see that when I do not restrict the partition and read the whole table?

I read the partition docs and it does not give examples of partition wise join explain plans.

explain plan for

    select count(*)
    from hash_test_a a, hash_test_b b
   where a.object_id = b.object_id

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |

---

| 0 | SELECT STATEMENT | | 1 | 7 | 5 (20)| 00:00:01 | | | | | |
| 1 | SORT AGGREGATE | | 1 | 7 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 7 | | | | | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 7 | | | | | Q1,02 | PCWP | |

|*  5 |      HASH JOIN            |             |   121 |   847 |     5  (20)| 00:00:01 |       |       |  Q1,02 | PCWP |            |


|   6 |       PX RECEIVE          |             |   121 |   363 |     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

|   7 |        PX SEND HASH       | :TQ10000    |   121 |   363 |     2   (0)| 00:00:01 |       |       |  Q1,00 | P->P | HASH       |

|   8 |         PX BLOCK ITERATOR |             |   121 |   363 |     2   (0)| 00:00:01 |     1 |     1 |  Q1,00 | PCWC |            |

|   9 |          TABLE ACCESS FULL| HASH_TEST_A |   121 |   363 |     2   (0)| 00:00:01 |     1 |     1 |  Q1,00 | PCWP |            |

|  10 |       PX RECEIVE          |             |   131 |   524 |     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

|  11 |        PX SEND HASH       | :TQ10001    |   131 |   524 |     2   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |

|  12 |         PX BLOCK ITERATOR |             |   131 |   524 |     2   (0)| 00:00:01 |     1 |     1 |  Q1,01 | PCWC |            |

|  13 |          TABLE ACCESS FULL| HASH_TEST_C |   131 |   524 |     2   (0)| 00:00:01 |     1 |     1 |  Q1,01 | PCWP |            |

--------------------------------------------------------------------------------------------------------------------------------------

  explain plan for
       select /*+ parallel (a,8) parallel (b,8) */ count(*)
       from hash_test_a partition(HASH_TEST_A01) a, hash_test_c partition(HASH_TEST_A01) b
      where a.object_id = b.object_id

--------------------------------------------------------------------------------------------------------------------------------------


| Id  | Operation                 | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT          |             |     1 |     7 |    15   (7)| 00:00:01 |       |       |        |      |            |

|   1 |  SORT AGGREGATE           |             |     1 |     7 |            |          |       |       |        |      |            |

|   2 |   PX COORDINATOR          |             |       |       |            |          |       |       |        |      |            |

|   3 |    PX SEND QC (RANDOM)    | :TQ10000    |     1 |     7 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE        |             |     1 |     7 |            |          |       |       |  Q1,00 | PCWP |            |

|   5 |      PX PARTITION HASH ALL|             |   999 |  6993 |    15   (7)| 00:00:01 |     1 |     8 |  Q1,00 | PCWC |            |

|*  6 |       HASH JOIN           |             |   999 |  6993 |    15   (7)| 00:00:01 |       |       |  Q1,00 | PCWP |            |


|   7 |        TABLE ACCESS FULL  | HASH_TEST_A |   999 |  2997 |     7   (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWP |            |

|   8 |        TABLE ACCESS FULL  | HASH_TEST_C |   999 |  3996 |     7   (0)| 00:00:01 |     1 |     8 |  Q1,00 | PCWP |            |

--------------------------------------------------------------------------------------------------------------------------------------

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

Received on Wed Jul 19 2006 - 15:27:36 CDT