RE: Help CBO ANOMALY!!! - BUG WITH HASH_AREA_SIZE? :)
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <paulastankus@xxxxxxxxx>
- Date: Tue, 6 Jun 2006 16:37:23 +0200
Paula
>the issue I had was not that it choose hash join but that setting the
>hash_area_size to a "smaller" size in the session worked better than
>the parameters on the database - I don't understand that at all.
As I already wrote the costing formula of the CBO depends on hash_area_size. If
you increase hash_area_size the cost of the hash join decrease. This, be
careful, is independent of where you set it! I.e. not because a value is set at
session level it's better than a value set at database level...
That said, in your case you have better performance with a nested loop because
the CBO performs a wrong estimation. Just compare the estimated cardinalities
(1033/1027) and the real number of rows returned by the statement (12). In your
case, I guess, the large number of predicates (which eventually contains some
correlated data as well...) lead to this behavior.
HTH
Chris
--
//www.freelists.org/webpage/oracle-l
Other related posts:
