upgrade to 11.2.0.3 - performance issues
All,
We had all working fine until we moved to 11.2.0.3. Earlier - We have a query that joins to an inline view. In our old production system (11.1.0.7) the query takes 3 seconds and performed a nested loop. In the new production (11.2.0.3) with the same data, the query is taking 90 seconds to return and the query is performing hash_join.
I am wondering, why oracle is using hash join instead of nested on 11.2.0.3? earlier version of oracle 11.1.0.7 was using nested loo.
also what might influence oracle to use the hash join instead of nested loop?
is there a way, i can have oracle to use nested loop instead of hash join?
We had all working fine until we moved to 11.2.0.3. Earlier - We have a query that joins to an inline view. In our old production system (11.1.0.7) the query takes 3 seconds and performed a nested loop. In the new production (11.2.0.3) with the same data, the query is taking 90 seconds to return and the query is performing hash_join.
I am wondering, why oracle is using hash join instead of nested on 11.2.0.3? earlier version of oracle 11.1.0.7 was using nested loo.
also what might influence oracle to use the hash join instead of nested loop?
is there a way, i can have oracle to use nested loop instead of hash join?
0