The query is taking 20 mintues to run. It's a view -which has a select statments
You'll see HUGE differences in the two plans, centered around the Hash Match operation on the upper middle right. The Estimated plan sees very little costs throughout the plan, while the actual plan shows loads of costs, especially the 8Gb of data being churned by the Hash Match (about 28m rows which it iterates through twice).
So knowing nothing else, you definitely need some index and statistics refreshes.
But if you can share the actual code, that'd be even mo' better. =^)
Thanks,
-Kev
Also, it appears you are doing a "select distinct x from y order by z" which is consuming the 39%. My first question is…do you need to have the data sorted at this point? Would just doing a distinct work, and then sort at the end of the process? Also, without looking at the query, sometimes (very rarely) using a group by actually works faster than a distinct. However, this again is up to the data you are actually returning (and again, is very rare).
Try removing the order by and see what happens.
I have changed couple of indexes .now the query runs in 18 secs[link text][2]. can we improve this any thing more ? please find the actual plan attached