Cost differences - Help!!

From: Swedish Viking <oracledba_at_home.se>
Date: 2 Jun 2004 01:43:57 -0700
Message-ID: <8eae55e6.0406020043.13525823@posting.google.com>

Hi!

Can anybody give some advice on the differences that I see in my environments.

When I run my statement:

select * from a,b where a.id=b.id;

In one of my environents I get the following plan:
| Operation |Name|Rows|Bytes|Cost

---

| SELECT STATEMENT | |11M|916M|7156
| HASH JOIN | |11M|916M|7156
| PARTITION HASH ALL| | | |
| TABLE ACCESS FULL|a |11M|436M|188
| PARTITION HASH ALL| | | |
| TABLE ACCESS FULL|b |11M|510M|3756

In the 2:nd I get:

| Operation |Name|Rows|Bytes|Cost

---

| SELECT STATEMENT | |11M|910M|144266 |
| HASH JOIN |11M|910M|144266 |
| PARTITION HASH ALL| | | | |
| TABLE ACCESS FULL|A|11M|434M| 20081 |
| PARTITION HASH ALL| | | | |
| TABLE ACCESS FULL|B|11M|485M| 26435 |
And in the 3:rd I get:

| Operation |Name|Rows|Bytes|Cost

---

| SELECT STATEMENT | |10M|893M|140694
| PARTITION HASH ALL | | | |
| HASH JOIN | |10M|893M|140694
| TABLE ACCESS FULL|A |10M|426M|23913
| TABLE ACCESS FULL|B |11M|510M|31622

---

What causes the differences? Any ideas?
I have tried to change db_file_mulitblock_read_count without any changes in behaviour.

Best Regards / The Swedish Viking Received on Wed Jun 02 2004 - 03:43:57 CDT