Re: Tuning large aggregation query
- From: Sidney Chen <huanshengchen@xxxxxxxxx>
- To: "thomas.kellerer@xxxxxxxxxx" <thomas.kellerer@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Apr 2014 09:57:21 +0800
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@xxxxxxxxxx>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
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Regards
Sidney Chen
Other related posts:
