Comment below.
HJR
--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================
"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message
news:2687bb95.0203241703.4a3cce2_at_posting.google.com...
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
news:<a7jokl$l22$1_at_lust.ihug.co.nz>...
> > Daniel,
> >
> > This is wildly inaccurate. I can't believe that even you could post
this
> > drivel.
> >
> > It *is* the size of the memory sort area that is being written (copied,
if
> > you prefer) to disk. Therefore, Oracle's clear recommendation is that
> > extent size on the temporary tablespace should be sort_area_size or
> > multiples thereof (add one block to the extent size if using dictionary
> > managed tablespace). The size of the transaction has bugger-all to do
with
> > it, or what the original poster asked.
> >
> > "The size is likely too large.... I normally use something between 64K
and
> > 256K". Are you completely and utterly off your trolley? Ever heard of
the
> > statistics 'sorts (memory)' and 'sorts (disk)'? The recommendation from
> > Oracle is that the ratio of memory sorts to disk sorts should be over
95%.
> > You set sort_area_size to 64K or even 256K, and I'll guarantee you a
memory
> > to disk ratio in the region of a feeble 15%.
> >
>
> But Howard (based on observation) your typical tuned OLTP can get
> 99.9% of sorts in memory with a sort_area_size of only 128k so why
> assign unneeded OS memory to sessions that do not need it, especially
> since there are going to be some sessions that need large quantities
> of real memory?
Since, whatever you set sort_area_size to, all users get precisely zero sort
area, it doesn't matter particularly that you set it 10Mb or bigger. That's
the size the sort area can grow to, if it's needed. If there are users not
doing sorts, then they won't have a sort_area_size, and they won't be
wasting memory.
>Isn't it better to leave the extra memory in the OS
> for distribution as needed? Other than this small point I agree with
> everything you said in your two posts on this thread.
>
Well, yes it would be good to leave it to the O/S if setting the parameter
took it away from the O/S. But it doesn't.
Take this snippet from Steve Adams' website, for example:
"The myth that each process requires an area of memory equal to the
sort_area_size is wrong. Until a process begins a sort, it has not one byte
reserved for sorting. And the myth that sort_area_retained_size is retained
after each sort to be used for subsequent sorts is wrong also. No sort
memory is retained for subsequent sorts."
It grows to be the s_a_s during the sort, and then it shrinks back down to
nothing. So you can set the parameter large, and all you've done is set a
maximum target for a user's sort memory.
Best regards
HJR
>
> > To avoid sorts to disk, you want as big a sort_area_size as you can
possibly
> > manage. (Remember: it's dynamically re-configurable per session). So a
> > sort_area_size of 10M or larger wouldn't be out of place in the right
> > circumstances. But if a swap to disk is unavoidable, then you will
induce
> > additional I/O and hence worse performance if the extent size it finds
on
> > disk is anything other than an exact multiple of the sort_area_size.
> >
> > Any other advice is... well, it's Daniel Morgan really, isn't it.
> >
> > HJR
> > --
> > ----------------------------------------------
> > Resources for Oracle: http://www.hjrdba.com
> > ===============================
> >
> > "Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message
> > news:3C9CEDFE.669B9EB8_at_exesolutions.com...
> > > It is not the size of the memory sort area that is being copied ... it
is
> > > the size of the transaction. The size is likely too large ... and
likely
> > > irrelevant unless you have some horrible shortage of disk space. I
would
> > > normally use something between 64K and 256K but then that is because I
> > > have a sense of the size of the transactions.
> > >
> > > Daniel Morgan
> > >
> > > Alan wrote:
> > >
> > > > Hello,
> > > >
> > > > I'm currently working on a statement to create a temporary
tablespace
> > > > on my 8i DB. Here is what I have so far:
> > > >
> > > > CREATE TEMPORARY TABLESPACE temp
> > > > TEMPFILE '/u2/oracle/data/B/tempb.dbf' size 200m reuse
> > > > EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
> > > >
> > > > I picked a UNIFORM SIZE of 1M because my
> > > > SORT_AREA_SIZE = 1048576
> > > >
> > > > Am I wise to set the UNIFORM SIZE to 1048576?
> > > >
> > > > If Oracle needs to do a disk sort it will want to copy data from
> > > > my memory sort area into TABLESPACE TEMP.
> > > >
> > > > When it does this copy, will it be happy to see an extent
> > > > which matches the SORT_AREA_SIZE exactly?
> > > >
> > > > Or would the copy go quicker if I setup temp so each extent
> > > > is padded a few bytes larger than SORT_AREA_SIZE?
> > > >
> > > > A variation on this question is this:
> > > > Should each extent by a multiple of SORT_AREA_SIZE?
> > > >
> > > > If yes, should the extent be 'padded' by a few bytes?
> > > >
> > > > Alan
> > >
>
> Minor point inserted above. -- Mark D Powell --
Received on Sun Mar 24 2002 - 21:39:34 CST
