Re: Daniel Morgan and Syband Bakker

In article <afek04$v5e$1_at_lust.ihug.co.nz>, "Howard J. Rogers" <dba_at_hjrdba.com> wrote:

>The sort area *isn't* in the UGA. At least, not all of it is, and not the
>bit used for the initial sort. The bit that goes into the UGA is the bit
>referenced by sort_area_retained_size, which is used at the end of the
>primary sort, during the merge phase -when partial sorts (the 'sort runs')
>are merged into a single, fully sorted, result set immediately prior to
>return to the user.

So, are you saying it allocates memory in both places? Or does it allocate it here (PGA) and copy it there (UGA) after the sort? If it's allocated in the UGA, it's taking up space in the UGA, used in the sort or not.

You (and Tom) keep speaking of S_A_R_S (I'm getting tired of typing it), as if it's used to allocate memory. From what I'm reading in Oracle's docs, it's not during allocation that it comes into effect. It's during the DE-allocation of memory that S_A_R_S limit is used. I could point you to the same links, the same quotes, I've been showing to Tom, but I think you've probably seen those by now.

>Oracle's standard advice for MTS configuration seems to be to set
>nsort_area_retained_size to half the sort_area_size. Therefore, in a
>standard-configured MTS, the bulk of the total amount of memory used to
>perform sorts is going to be in the PGA, attached to the Server Processes,
>and thus not in the SGA at all.

What strikes me funny about this is that Oracle's default for S_A_R_S is S_A_S, standard advice or no. So if you don't set it, it sounds like you're saying it's all going to be UGA.

>So, to nitpick, a more accurate statement would have been "SOME OF the sort
>area IS the SGA, but most of it isn't".

That's the most accurate description of it, it seems. It's a shame Oracle's training materials aren't as clear... if anything, they're apparently just flat wrong, Received on Thu Jun 27 2002 - 08:10:16 CDT