Re: sort_area_retained_size sizing?

Hi!

> I've gone looking for guidelines/info to assist in tuning this particular
> parameter, but am getting conflicting information.
>
> - In a post by guru Howard J. Rogers to c.d.o.s. regarding a thread where
> someone specifically asks how to configure these two parameter, He states
> that he typically configures these two parameters to be the same (but
> offers no real reason why).

In my understanding, retained sort area was invented only for the reason that if you finish sorting and fetch your records during longer time, then there's no reason to keep all sort_area_size of memory allocated. Retained sort area is used just for buffers for retrieving already sorted results from disk.
So, in a system with lots of concurrent users and large sort resultsets (which are retrieved "slowly") there could be point setting sort_area_retained_size smaller than sort_area_size. In a typical OLTP system however, I don't see much of a reason to do that.

Also, keep in mind that if your sort_area_retained_size is smaller than sort_area_size, but the sorted resultset is bigger than retained size, the resultset is written to disk (temporary tablespace) from sort_area_size first in order to free sort area memory, and then read back in smaller chunks through retained sort area. If the resultset "fits" into retained size, it's delivered back to user immediately. So there are drawbacks in setting retained size too small, which could mean additional, unnecessary IOs.

>
> - The concept guide in the Oracle Doc set though seems to indicate that
> each user performing a sort grabs "sort_area_retained_size" worth of
> memory, and thus recommends NOT sizing it the same as sort_area_size
> on systems with a large number of concurrent users.

No sort memory is allocated in standard block sizes according to my understanding, so in 8kb db_block_size system sort area is allocated in increments of 8k up to sort_area_size and released with free() call afterwards (note, this doesn't mean that this virtual memory will be available to other processesses in current case)

Visit Steve Adams's site www.ixora.com.au for more information, also you can use 10032 trace to find more information on your own.

Tanel.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).