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

"Swedish Viking" <oracledba@home.se> schrieb im Newsbeitrag
news:8eae55e6.0406020043.13525823@posting.google.c om...
> 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

OK, now we have the execution plans, but
You did not provide Your Version(s)
of Your "three environments" !

Are You sure that the CBO statistics are the same on all 3 envs ?
The data can be much different, too. Or do You have clones ?

Just a guess from scratch.

For more help, more details are needed.

hth, Jan

The most important difference is that
the two tables in the third environment
are properly "equi-partitioned" so you
are getting a partition-wise join.

Either the numbers of partitions, or the
partition boundary values have slipped
in the first two environments, so that the
partitions from the two tables cannot be
aligned.

Given that the third environment is structurally
different from the other two, I suspect the
dramatic difference in the costs between
the first two is down to another difference
in environment, such as a dramatic difference
in the accuracy of the statistics, a huge difference
in the value of the db_file_multiblock_read_count,
system statistics being active in one system and
not the other, and so on.

--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"Swedish Viking" <oracledba@home.se> wrote in message
news:8eae55e6.0406020043.13525823@posting.google.c om...
> 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

On Wed, 02 Jun 2004 01:43:57 -0700, Swedish Viking wrote:

> 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


Which version of the database? Plans look exactly the same, the only
problem that I have is with the number of rows. You probably ran
analyze with estimate option, which gives almost exact results. Also,
if it is version 10g, your system statistics is initialized to some
insane values, which do influence query cost. As for vikings, you are
a welcome change to the Danish variety. There are some really tough
raiders from Denmark, gathered around the BARF clan.

--
Trust me, I know what I'm doing. (Sledge Hammer)