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

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

> 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 - 19:03:38 CST