how to tell if you are getting a partition wise join
- From: ryan_gaffuri@xxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 19 Jul 2006 20:27:36 +0000
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 | |
--------------------------------------------------------------------------------------------------------------------------------------
Other related posts:
