extent size for temp tablespace

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?

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.

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

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

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

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]

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!

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 ( http://oradoc.photo.net/ora81/DOC/se...1/c02block.htm ) 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?

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!

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

Pando -
I am looking at the ILT Notes for the O8: Performance Tuning Workshop course (Part #M08298, dated March 1999), p. 10-27:

"Select INITIAL and NEXT values as integer multiples of SORT_AREA_SIZE, allowing an extra block for the segment header."

I believe this is just another example of poorly worded notes. As the above citation notes, the extra block is for the SEGMENT header, of which there is only one. Therefore, I would recommend you disregard references to the extra block.

Tycho - Pando is ALMOST right about the one temporary segment per tablespace. There is only one temporary segment created within a TEMPORARY tablespace for each instance mounting the database. So if you are in an OPS environment with three instances mounting the same database, it is possible to have up to three temporary segments in a TEMPORARY tablespace. It is also possible to have more than one TEMPORARY tablespace within a database. Consequently, if you look at V$SORT_SEGMENT, you may see more than one temporary segment listed.

Tycho
only one segment is created in a temporary tablespace and this is the beauty of why setting a tablespace temporary and not permanent, extents of the segment is used over and over again which consequently rules out the necessity of allocating extents and deallocating extents in a temporary segment which leads to better perfomance in a sort intensive (Datawarehouse?) enviroment.

ops sorry didnt read carp´s post :o
I havent worked with OPS so... heh :D

Thank you Pando and Carp,

I learned something new here.

BTW I finished my OCP 60 minutes ago passing with 55/62 for Networking.

Tycho

Hi!
Just 2 cents :)
-A temporary segment has 1 block header
-the next extents hasn't

Supose a block_size=1K
If you define initial 64k and next 64k then the first two extents of this segment will be 65k and 64k, so , you will have 2 sizes of extents in the same tbs, that's why the fragmentation.
The best way is
initial 64K next 65K

Hope I'm clear :D

I just test that rcaballe, I do see the extents uniform not as you just suggested though, my initial is 64K and next is 64K and the rest are all 64K, i.e uniform size extents. May be it´s because I used minimum extent 64K clause?

Where do you check the extents?
In dba_extents or dba_segments, I think is different.
If you say initial 1M in dba_segments will say 1M, but in dba_extents?

tested with tablespace manager

rcalle -
You have it backwards.
The first block of the first extent is allocated as the segment header. Consequently, if you have the 1K block size and specify
INITIAL 200K NEXT 200K PCTINCREASE 0
all of your extents will be 200 blocks long. However, your first extent will only have 199 blocks available for data storage.

that´s what i though too.... otherwise all initial of any type of segment would require an extra block...