Re: need help tuning a very large delete

Ryan Gaffuri wrote:

>
> so your saying the tuning course says not to have a high
> sort_area_size if your doing parallel query? Wow....

Uh huh. Interesting, isn't it? (Well, 1 MB is quite big, I suppose. They're just saying it seems non-productive to go bigger than that. I guess it's because there's an inherent limitation with parallelism, which is that the slaves have to message each other to co-ordinate their activity, and that messaging is a bottleneck. Throw more sort memory at the slaves, and they won't be able to make great use of it because the messaging holds them back from doing so. But that's not in the notes, so I'm just guessing on that one. Jonathan???!).

>
> I thought sort_area_retained size was what Oracle reduced the
> sort_area_size to similiar to optsize in v$rollstat.
>

Classic error. It's what gets used for the merge phase of a query (when you bring back the swapped-to-disk partial sort runs and merge them into a single, totally sorted result set). It's not 'what happens to sort_area_size at the end of a sort' at all.

> so your saying that 'sort_area_size' is the amount per sort, not per
> session?

Correct, that's what I'm saying, because that's my understanding (and what the course notes say... was it you going on the Performance Tuning course soon? Then check out Chapter 9, page 15).

>So in a parallel query I can have 2 x sort_area_size per
> slave right?

Worst case, yes. It's what you should allow for, anyway.

>
> in a non-parallel session I can have n X sort_area_size where n=
> number of sorts.

Correct.

Regards
HJR Received on Fri Sep 19 2003 - 16:18:39 CDT