Tuning By cardinality Estimates

From: hrishy <hrishys_at_yahoo.co.uk>
Date: Fri, 6 Mar 2009 09:41:39 +0000 (GMT)
Message-ID: <342793.86887.qm_at_web23707.mail.ird.yahoo.com>


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 .



Received on Fri Mar 06 2009 - 03:41:39 CST

Original text of this message