temp tablespace initial/next

Hi Friends,

I had an Oracle Conultant in my company yesterday and the subject of Temp tablespace cropped up.

I have my initial extent size set to a multiple of sort area size + 1 block.

I have my next extent size set to a multiple of sort area size.

e.g.

initial : 794624
next : 786432

The consultant told me that initial and next should be the same in Temp tablespace.

He told me that every time a session required a sort that went to disk, the extent size added to the sort segment would take initial_extent first 794624 and then next extent, 786432.
and this would happen for every sort that went to disk for every session that required a sort, thus heavily fragmenting temp tablespace.

I always thought that the initial extent size is at the segment level. So an initial extent of 794624 would be created when sort segment is created. and all subsequent extents would be 786432, regardless of whether a different session is requiring a sort or not.

Someone out there please enlighten me.

I think I have answered my own question.

According to this query I am right.

1 select bytes, count(bytes)
2 from dba_extents
3 where tablespace_name = 'TEMP'
4* group by bytes
SQL> /

BYTES COUNT(BYTES)
---------- ------------
786432 55


Database has been up for 2 days, more than 1 session has requested a sort. Maybe I should email him with this.


[Edited by Sureshy on 06-26-2002 at 06:26 AM]

Firstly, Create Temporary Tablespace ... Extent Management Local will always be better than Create Tablespace ... Temporary.

Secondly, setting of Initial = Next and Pctincrease =0 for datafile of temporary tablespace is always a good way to eliminate fragmentation. However, I think a sort segment will seldom deallocate extents, so it will rarely be fragmental even though you set PctIncrease != 0.

Please correct me if I am wrong.

i 100% agree with Calvin_Qiu

OCP too

I was not asking about how to set up a temporary tablespace or LMT or the advantages of LMT, I am already aware of that!

I was asking about Dictionary tablespaces and the discussion I had with an Oracle consultant. I was asking whether or not he was correct in his statement, to establish whether or not my company should hire him as a consultant for advice when I am not available.

I'll repeat what he said, all I want is for someone to agree or disagree with this statement.
He said :
Every session that adds extents to the temporary sort segment always adds the value of initial_extent to the chain of current extents and all subsequent extents will be sized at the value of next_extent FOR THAT SESSION. If this is so you would have a sort segment with different extent lengths all the way through it.

I was saying that initial extent is at the segment level and not the session level, regardless of whether it is a temp tablespace or not! There is only one extent at the value of initial_extent and that is the first one. All subsequent extent sizes are set at the value of next_extent no matter which session causes its creation in temp tablespace.

If you read Oracle documentation regarding DMT TEMP tablespaces, it states that the extent size of the sort segment should be a multiple of sort_area_size + 1 block. Hence, my initial_extent is a multiple of sort_area_size + 1 block and next_extent is a mulitple of sort_area_size alone.

P.S. The consultant charges £1200 a day. I want to make sure he knows his stuff.






[Edited by Sureshy on 06-26-2002 at 10:33 AM]

I thought it was on an instance basis, not a session basis. However, there seems to be some ambiguity in the documentation. (http://otn.oracle.com/docs/products/...block.htm#2922)

Quote:

---

A single sort allocates its own temporary segment, in the temporary tablespace of the user issuing the statement, and then returns the extents to the tablespace.

Multiple sorts, however, can use sort segments in a temporary tablespace designated exclusively for sorts. These sort segments are allocated only once for the instance, and they are not returned after the sort but remain available for other multiple sorts.

---

That being said, I think your idea is closer than his.

agree with marist89. I wanna know what's "single sort"?