Re: sort_area_size / sort_area_retained_size

From: Stan <stan0074_at_yahoo.com>
Date: Fri, 05 Apr 2002 04:28:39 GMT
Message-ID: <3CAD28BB.5080001@yahoo.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" <stan0074_at_yahoo.com> wrote in message
> news:aaff1cfe.0204041735.275234ce_at_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" <dba_at_hjrdba.com> wrote in message
>>

> news:<a8grgk$9kt$1_at_lust.ihug.co.nz>...
>

>>>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" <stan0074_at_yahoo.com> wrote in message
>>>news:3CABDE59.8080404_at_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
>>>>>
>>>>>

>
>
Received on Thu Apr 04 2002 - 22:28:39 CST