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

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

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

>