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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
