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

No. That advice applies to temporary tablespaces which happen to be dictionary managed, because the initial extent has to allow for the segment header block.

But the poster has indicated that he is using 8i temporary tablespaces in locally managed tablespaces, and in such tablespaces, the advice from Oracle is that the uniform size parameter should *equal* the sort_area_size or a multiple thereof, *without* the addition of the extra block. Since extent allocations are handled differently in locally managed tablespaces, there is *no* need for the extra space required in dictionary managed versions of temporary tablespace.

So, to answer the original poster, no... stick to the exact setting of sort_area_size (or an integer multiple thereof), and don't pad it with extra bytes at all. You will pay a performance penalty if you do.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Sean Comar" <skbansal999_at_directvinternet.com> wrote in message
news:3c9d60b9$1_3_at_nopics.sjc...


> I had read in some Oracle documentation that typically you will add one

> oracle block to sort_area_size to get uniform extent size.

> Say, Oracle block size = 8192 bytes

> Then, in your case, Uniform extent size should be set to 1056768

>

> "Alan" <alainrtv_at_yahoo.com> wrote in message

> news:fbee32ac.0203221907.324821c4_at_posting.google.com...

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

>

>