Re: SORT_MEM_SIZE ... when allocated?

Hi

    If I am guessing correctly you are trying to mention SORT_AREA_SIZE instead of sort_mem_size.. . Also one thing I would like to make clear that

PGA is a part of memory which will hold the data and control information of a
single oracle process. In dedicated server architecture PGA is not shareable. But
in the case of multi-threaded architecture part of PGA may be stored in SGA.. where
multiple user processes use the same server process to reduce the database memory requirement. Which means data and control info of a particular user is
stored in SGA in case of MTS.

    Now if your query needs to sorted then it needs sort area which resides in the
memory of user process. This sort area will grow according to accommodate the
amount of data to be sorted but limited by the value of SORT_AREA_SIZE. Once the
sorting is done the memory used is released up to the value determined by SORT_AREA_RETAINED_SIZE. Here, keep in mind that the term memory released does not means it releases memory to OS , It releases memory within itself so that same
user process can use released memory to some other purpose.

   According to server concept the minimum value of SORT_AREA_RETAINED_SIZE

is the equivalent of one database block. Also the sort area space will be released when
the last row from the sort area is fetched.

   Hope it help...
    Good luck.

Pradhan.

Andy Dreisch wrote:

> I understand SORT_MEM_SIZE is allocated through the PGA, not the SGA.
> But I've read varying accounts as to *when* the memory is allocated.
>
> Is SORT_MEM_SIZE allocated at session start time, or is it allocated
> only one first need, i.e., the first sort in a session?
>
> Also, if SORT_???_RETAINED_SIZE (forgot name) is 0, does this mean that
> memory previously allocated for sort is immediately freed?
Received on Thu Jan 28 1999 - 00:34:06 CST