RE: Temporary Tablespace Design

Hi Alex,
 Yes, Thanks for catching it. I got mixed up in explaining the memory allocation properly and left out some things. Sorry.   What you say, is what happens in the very first sort as far as allocating memory in chunks (until sort_area_size is reached) is concerned. When the very first sort operation completes, 'sort area' is left in PGA (UGA for MTS) with the size equal to sort_area_retained_size (after deallocating additional memory, whenever the free() call is executed). Subsequent sort operation will start with 'sort area' of the size equal to sort_area_retained_size and start allocating memory when needed till it reaches the max allowed (sort_area_size). Keeping these two areas of the same value avoids this memory allocation/deallocation process, provided there is enough memory available to do so. Also, the sort_area_retained_size is used in the 'fetch' phase of a sort opeation. So, if the sort had to use disk, then it will help reduce the number of I/Os. This is my understanding of how sort is handled. Anyone knowing more 'internal' workings of sort, please let me (and us) know so I can get this straight.

Thanks.

• Kirti Deshpande Verizon Information Services http://www.superpages.com

> -----Original Message-----
> From: Hillman, Alex [SMTP:Alex.Hillman_at_usmint.treas.gov]
> Sent: Wednesday, April 18, 2001 10:37 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Temporary Tablespace Design
>
> I think you are not correct. First - Oracle allocates memory for sorting
> as
> needed by 8K chunks up to sort_area_size. Second if your sort_area_size is
> large enough to do sort in memory and your sort_area_retained_size <
> sort_area_size oracle will dump sorted data into temporary tablespace and
> then read from this tablespace. So it is a tradeoff between late memory
> release and temporary tablespace I/O.
>
> Alex Hillman
>
> -----Original Message-----
> Sent: Wednesday, April 18, 2001 10:40 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi John,
> As you mentioned, it depends on a number of factors. I believe I replied
> to
> a question about improving performance of index building after an import.
> Anyway, here is the reasoning behind why I said that.... When Oracle
> begins
> sorting, it starts with the memory equal to sort_area_retained_size value
> and may eventually acquire memory equal to sort_area_size. This involves
> at
> OS level, malloc (and may be some other) memory mgmt routine(s). After the
> sort phase is complete, Oracle will trigger memory de-allocation and the
> OS
> will go at work again. If there are servile sessions performing sorts,
> there
> can be an increased OS level memory mgmt activity (while attaining
> sort_area_size and releasing it back to sort_area_retained_size) . But if
> the server has *enough* memory keeping sort_area_retained_size equal to
> sort_area_size may actually help. I also assume that these days most
> installations have ample memory. And that the advice of using 50% (which I
> had heard of) of sort area size for sort area retained size should always
> be
> viewed in light of one's requirements and resource availability. But I
> can't
> stop wondering... how come it was 50% and not 10% or 20%.. so it's all
> relative. I have been using what I suggested, for a number of years now
> with no problems or complaints.
>
> Cheers !
>
> - Kirti Deshpande
> Verizon Information Services
> http://www.superpages.com
>
> > -----Original Message-----
> > From: Hallas, John [SMTP:HallasJ_at_logicae.com]
> > Sent: Wednesday, April 18, 2001 6:21 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Temporary Tablespace Design
> >
> > FOR YOUR INFORMATION
> >
> > ESIS and EPFAL are now part of Logica. The Internet email addresses of
> the
> > staff has changed to the following - lastnameinitial_at_logica.com eg
> > SMITHK_at_logica.com. Emails using the old format will continue to be
> > delivered until 30th June 2001.
> >
> >
> > Kirti Deshpande writes
> >
> > "Also, keep SORT_AREA_RETAINED_SIZE the same as SORT_AREA_SIZE. As the
> > former will be used while fetching data after sort phase is completed."
> >
> > I thought best advice was to make the retained size smaller (50%?) of
> > sort_area_size to allow initial sort memory to be released after the
> first
> > part of the sort is managed and only the merge phase of a disk sort is
> > left
> > to do.
> >
> > I appreciate that a lot depends on the amount of memory available and
> the
> > number of concurrent processes that may be performing sorts but surely
> the
> > aim is to free memory up as soon as possible down to the
> > sort_area_retained_size
> >
> > Thanks
> >
> > John
> >
> > (PS I do apologise for the rubbish at the top of this mail but it is
> > inserted after I have sent the mail into our mail gateway)
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).