Re: Sort Area Size

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 29 Jun 2002 09:54:16 +0100
Message-ID: <1025341278.8484.0.nnrp-14.9e984b29@news.demon.co.uk>

I think I want to modify your answers slightly. I'm going to go back and repeat the tests, but my observations in the past have been that any sorting starts by allocating space in the UGA, and once the in-memory demand exceeds the sort_area_retained_size the excess is allocated from the PGA. Then the UGA space is left open after the PGA space is released so that the UGA can be used to pipeline the result set onwards.

The test method is simply to set

sort_area_size = 8M
sort_area_retained_size = 2M

Start a session, and do some large
sorts, and watch v$sesstat figures,
specifically the UGA memory and PGA
memory ones - cross reference to the
memory usage quoted at the O/S; and
repeat for dedicated and shared servers.

It is sensible to use fairly large figures and fairly large sorts to avoid being
fooled by side-effects of (e.g.) parse-
related memory allocation. Taking
deltas from a known starting point is
also a good idea.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Thomas Kyte wrote in message ...


>In article <aff1pp$2t5$1_at_cronkite.cc.uga.edu>, jeff_at_work.com says...

>fine, tell me then, if

>

>sort_area_size = 1m

>sort_area_retained_size = 64k

>

>and you have MTS (shared server)

>

>a) how much UGA memory will be allocated at most for the sort

>b) how much PGA memory will be allocated at most for the sort

>c) will the contents of the PGA memory be MOVED to the UGA (since PGA is in

the


>process and UGA is in the SGA)

>

>Answers

>

>a) 64k -- for the stuff that is retained after the sort. at most, could be

0 if


>this was an internal sort and didn't need to be fetched by the client.

>b) 1m -- at most, for the sort.

>

>c) yes, since the sort actually takes place in the PROCESS (shared server)

but


>the results need to be in the SGA (for the client to fetch from)

>

Received on Sat Jun 29 2002 - 03:54:16 CDT