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 - 21:43:24 CST
