Path: news.easynews.com!easynews!news-out.cwix.com!newsfeed.cwix.com!newsfeed.mathworks.com!wn3feed!worldnet.att.net!204.127.198.204!attbi_feed4!attbi.com!rwcrnsc54.POSTED!not-for-mail Message-ID: <3CAD28BB.5080001@yahoo.com> From: Stan User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:0.9.4) Gecko/20011128 Netscape6/6.2.1 X-Accept-Language: en-us MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server Subject: Re: sort_area_size / sort_area_retained_size References: <3CAA7B3F.3000002@yahoo.com> <3CAA7B3F.3000002@yahoo.com> <3cab29fb$1@usenetgateway.com> <3CABDE59.8080404@yahoo.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Lines: 145 NNTP-Posting-Host: 12.234.204.72 X-Complaints-To: abuse@attbi.com X-Trace: rwcrnsc54 1017980918 12.234.204.72 (Fri, 05 Apr 2002 04:28:38 GMT) NNTP-Posting-Date: Fri, 05 Apr 2002 04:28:38 GMT Organization: AT&T Broadband Date: Fri, 05 Apr 2002 04:28:39 GMT Xref: easynews comp.databases.oracle.server:142114 X-Received-Date: Thu, 04 Apr 2002 21:26:25 MST (news.easynews.com) excellent reply...as always thanks for your immediate reply, Stan Howard J. Rogers wrote: > Comments below > Regards > HJR > -- > ----------------------------------------------- > Resources for Oracle : http://www.hjrdba.com > =============================== > > "Stan" wrote in message > news:aaff1cfe.0204041735.275234ce@posting.google.com... > >>Hi howard, >> >>If i set my sort_area_size to 10m, then whenever the demand for sort >>is finished, won't it retain the said memory in >>sort_area_retained_size ? >> >> > > No, Stan. That's not what the word 'retained' means. The retained size is > what is held by the system after the initial collection of the rows, in > order to be able to perform the merge phase (where the partial sorts swapped > to disk are merged together and fetched back to you as a single, sorted > collection of rows). Once the merge phase has completed, retained_size drops > back to zero, too. > > In other words, retained_size is memory that's retained for final processing > of the sort, not retained for the lifetime of a session. > > No one has any sort_area memory at all, retained or otherwise, unless they > are using it. > > >>Also, what percentage of sort_area_size should be kept to >>sort_area_retaied_size in general? any caveats. >> > > I always make retained 100% of the basic sort_area_size. > > Regards > HJR > > > >>thanks in advance. >>Stan >> >> >>"Howard J. Rogers" wrote in message >> > news:... > >>>Stick 'em up high. If you've got lots of disk sorts, the sort_area_size >>> > is > >>>too low. Throw some more numbers at it, and they should go away -or most >>> > of > >>>them at any rate. >>> >>>Bear in mind that until a user starts actually doing a sort, s/he >>> > doesn't > >>>actually possess any sort_area_size at all, whatever the parameter file >>>might say. It's a limit to which the thing can grow on demand -and once >>> > the > >>>demand's finished, it shrinks back down. >>> >>>10Mb sounds like a good starting point to me. >>> >>>Regards >>>HJR >>> >>> >>>"Stan" wrote in message >>>news:3CABDE59.8080404@yahoo.com... >>> >>>>actually we have a hybrid of oltp and dss in a database and right now >>>> > i > >>>>have 1mb for sort_area_size and 512k for sort_area_retained_size. >>>> >>>>markag wrote: >>>> >>>> >>>>>Stan wrote: >>>>> > I see lots of disk sorts on my statpack reports, so how do i >>>>> >>> determine >>> >>>>> > the average sort_area_size and sort_area_retained_size that >>>>> > needs to > >>>>> > increased based on concurrent disk sorts ? >>>>> > Any advise! >>>>> > thanks in advance, Stan >>>>> >>>>> >>>>> >>>>>What are they now? recommendations for OLTP systems can be 64K to >>>>> > 256K; > >>>>>Batch processes are higher, 20-50M. My OLTP setting is actually 2M. >>>>> >>>>>You just need to play with it... >>>>> >>>>>This from an Oracle book I am reading....it really depends on # of >>>>>users, total physical memory, size of the process being performed, >>>>>etc. Total Physical Memory is the key though. You usually want >>>>> > Oracle > >>>>>to use as much memory as possible without forcing swapping to disk >>>>> > at > >>>>>the OS level. >>>>> >>>>>Anyway, the two params should be set to the same value usually. >>>>> >>>>> >>>>> >>>>>-- >>>>>Posted via dBforums >>>>>http://dbforums.com >>>>> >>>>> > >