Re: Cost differences - Help!!

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_at_home.se> wrote in message
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