Tuning By cardinality Estimates

  • From: hrishy <hrishys@xxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 6 Mar 2009 09:41:39 +0000 (GMT)


I am trying to tune a query which is attached along with the plan.
In some of the steps the estimated cardinality and actual rows is way off .

esp step 7,8,9,10,11

As a result of the wrong cardinality estimation instead of a hash join the 
optimizer chooses Nested Loops and the query takes almost 7 minutes to complete.

I resort to use_hash hint and the query completes in 2 minutes.

My question how do i make the optimizer aware of the correct cardinalities.
I have collected stats fresh .



Other related posts: