Path: news.easynews.com!easynews!hub1.nntpserver.com!hub1.meganetnews.com!newsfeed.sjc.globix.net!cyclone-sf.pbi.net!64.245.249.51!sfo2-feed1.news.algx.net!allegiance!news-hog.berkeley.edu!ucberkeley!enews.sgi.com!news.xtra.co.nz!newsfeeds.ihug.co.nz!lust.ihug.co.nz!ihug.co.nz!not-for-mail From: "Howard J. Rogers" Newsgroups: comp.databases.oracle.server Subject: Re: sort_area_size / sort_area_retained_size Date: Fri, 5 Apr 2002 13:43:24 +1000 Organization: ihug ( New Zealand ) Lines: 109 Message-ID: References: <3CAA7B3F.3000002@yahoo.com> <3CAA7B3F.3000002@yahoo.com> <3cab29fb$1@usenetgateway.com> <3CABDE59.8080404@yahoo.com> NNTP-Posting-Host: p10-tnt8.syd.ihug.com.au X-Trace: lust.ihug.co.nz 1017978284 22990 203.173.147.10 (5 Apr 2002 03:44:44 GMT) X-Complaints-To: abuse@ihug.co.nz NNTP-Posting-Date: Fri, 5 Apr 2002 03:44:44 +0000 (UTC) X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Xref: easynews comp.databases.oracle.server:142110 X-Received-Date: Thu, 04 Apr 2002 20:42:29 MST (news.easynews.com) 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 > > > > > > >