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