Tuning By cardinality Estimates

Hi

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 .

regards
Hrishy


      

Other related posts: