Re: sort_area_retained_size sizing?

Btw, this is a simplification, in parallel execution environment, the situation is a different (as can be read from ixora).

Also, I missed a comma in my previous post, "No sort memory" should be "No, sort memory".

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, November 14, 2003 4:19 PM

> 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).
>

-- 
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).