I tried to tune query .
But only on the below step the query gets struck for longer time. I m trying to identify the concurrent step in the explain plan. But i could not find there.
"We do a SUM step to aggregate from Spool 9681 (Last Use) by way of an all-rows scan. Aggregate Intermediate Results are computed globally, then placed in Spool 9708."
Can you please explain the reason for the delay in the step . Do we have any remedy for the same ?
You haven't given us a lot of info to go on.
This explain text presumably comes from Viewpoint - the high spool numbers almost certainly mean it has been grabbed from TD using a monitor call. To match this to a 'normal' explain plan you should:
- in the full explain text that you get from VP find the lowest spool file number. [A]
- find the lowest numbered spool file in the 'normal' explain plan [B]
- assume the two spool files found above are the same spool file
Let's say [A] = 9650 and [B] = 1
In that case, spool 9681 from your plan fragment is probably: (9681 - 9650) + 1 = 32
Similarly, spool 9708 in your fragment is probably: (9708 - 9650) + 1 = 59
That will hopefully allow you to match this step to the one in a 'normal' explain plan.
Why that step is taking a long time is another matter.
It's doing an "all rows scan" of a spool 9681. How many rows in that spool file? How many AMPs on your system?
It's doing an aggregation. How unique are the column value(s) in the GROUP BY clause? How long are those columns?
If you start with the above we might be able to help you some more.
I identified the issue. There were many group by columns in the view in a report query which hampered the performance.
Thanks for the suggestion.