extent size for temp tablespace

[pando]

Hi ppl

There is a formula in Oracle ILT books which shows how to set extent size for temporary tablespaces

initial=next=(multiple of sort area size)+db_block_size

anyone know who the extent size must add size of a block?

[fraze]

Well this is the official reason (taken from DBA 8 exam questions) - to reduce the possibility of fragmentation. As I've been a DBA for all of 1 1/2 days if anyone can explain why I'd be fairly interested!!! :-)

Oracle8: Database Administration - Managing Temporary Segments
Oracle8 Database Administration: Manage Storage Structures TBT - Unit 3, Lesson 1, Topic: 3
Oracle8 DBA Handbook - Physical Database Layouts - 110-111

Temporary segments are based on the default storage parameters of the tablespace.
For the TEMPORARY tablespace, INITIAL and NEXT should be equal to each other and a multiple of
SORT_AREA_SIZE plus DB_BLOCK_SIZE to reduce the possibility of fragmentation.
PCTINCREASE should always be equal to zero.

[tycho]

hi Pando,

Because the sort will be written from memory to disc the size has to be a multiple of the sort area size.

Because the sort segment will need a header block adding one db block makes up for the extra little space needed.

tycho

[tamilselvan]

One extra block is required for all data files.
While sizing the data file, always add one extra block.

[pando]

yes I nderstand that it has to be multiple of sort area size, my question is about the extra block. While as sizing a datafile I understand an extra block is added to avoid useless space at end of the datafile.
However, the ILT suggests

INITIAL EXTENT SHOULD BE EQUAL TO NEXT EXTENT
and the sieze of these extents should be

MULTIPLE OF SORT AREA SIZE + DATA_BLOCK_SIZE

it´s suggesting the size of extent has to be multiple plus a block and not the size of the datafile! And this is the part i dont get it

[pwoneill]

the temporary tablespace allocations are overflows of what happens in the sort_area_size, which is why you want the sort_area_size multiple. the extra block you mentioned is for the header block for the temporary segment. at least accoriding to what i've read :)

[Edited by pwoneill on 11-14-2000 at 11:04 AM]

[pando]

errrrr pwoneill you dont get my point , I know for the segment you need block header but in a temporary tablespace only one segment is created which means you need a block header for the whole segment and not one block for each extent which is the formula suggesting!

[pwoneill]

I see your point now. I have a diagram in front of me (oracle 24x7 page 585 for those who have the book), that seems to imply that each extent has it's own header block, which would justify the formula. Later in the book it mentions the formula you gave, and the justification that I used (page 611).

However other things I'm looking at ( [url]http://oradoc.photo.net/ora81/DOC/server.815/a67781/c02block.htm[/url] ) say headers are segment level, which would imply the forumla is incorrect and allocating extents larger then they should be.

Can anyone clear this up?

[pando]

well if every extent need an extra block then all extents in any tablespace would have to use that formula too! Which they dont otherwise the clause minimum extent would be totally useless!

[tycho]

Hi

Are you sure Pando????
>in a temporary tablespace only one segment is created.

Whats in the v$sort_segment view then?

Naturaly the way space is used in the temporary tablespace is much different the way it is used in other tablespaces.
So I think you are mistaken here.
>well if every extent need an extra block then all extents in any tablespace would have to use that formula too!

Tycho