Tuning By cardinality Estimates

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

--
http://www.freelists.org/webpage/oracle-l




application/x-zip-compressed attachment: Tuning_By_Cardinality_Estimates.zip