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.
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.
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.