Re: Pad temp extents a few bytes larger than sort_area_size?

> "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."

But Then what is the use of Sort_Area_retained_Size. Can you clarify this point.

Thanks.

Regards,
Ganesh R

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:<a7m6ka$vs5$1_at_lust.ihug.co.nz>...
> 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 Mon Mar 25 2002 - 00:01:42 CST