Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SORT_MEM_SIZE ... when allocated?
In article <431-36AFE308-68_at_newsd-172.iap.bryant.webtv.net>,
andydreisch_at_webtv.net (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?
>
>
SORT_AREA_SIZE is allocated within the address space of the shadow process
that is used to perform your SQL (if you are not using MTS, which is a good
idea) and is allocated when needed. If your sort_area_retained_size is
less then sort_area_size, then sort area is shrunk to that size when the
sort is done. I've never experimented with sort_area_retained_size=0. I
usually leave it to be the same as sort_area_size so that no memory
allocation is needed if I want another sort. If MTS is used, then sort_area
is allocated from the shared pool, and is freed when the sort is done. If you
are using MTS, then it's a good idea to increase shared_pool_reserved_size for
the big allocations.
Mladen Gogala
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Jan 28 1999 - 11:45:38 CST