Re: Tuning large aggregation query

1. From the sql monitor report(http://pastebin.com/2UXhusyk), check the activity(%) column, the majority of elapse time is from line 13(BUFFER SORT) to line 17(INDEX FULL SCAN). The bottleneck seems on the NESTED LOOPS, not on the aggregation operation(line 8 SORT GROUP BY) as you said. If this is true, consider the base table have 4M rows, you may want try hash join, instead of the NESTED LOOPS. With hash join, the parallel execution should help more on performance.
2. Since IO waits on temp space is not sinificant, I'll not worry about the pga size setting.

On Tue, Apr 8, 2014 at 1:44 PM, Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>wrote:

> Sidney, 04.04.2014 15:31:
>
> > have you try Workarea_size_policy to manual and increase the
> hash_area_size/sort_area_size.
>
> Yes I tried. I increased both up to 512MB but unfortunately without any
> change.
>
> Regards
> Thomas
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Regards
Sidney Chen

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