Re: Cost differences - Help!!

"Swedish Viking" <oracledba_at_home.se> schrieb im Newsbeitrag news:8eae55e6.0406020043.13525823_at_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

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 Received on Wed Jun 02 2004 - 04:12:36 CDT