Re: Temporary tablespace and uniform extents

"Mladen Gogala" <mgogala_at_yahoo.com> wrote in message news:482af029$0$30633$834e42db_at_reader.greatnowhere.com...
> Once upon a time, common wisdom
> for sizing the
> extent of the temporary tablespace was SORT_AREA_SIZE + 2 blocks of
> overhead. What do we do now?

As so often happens, common wisdom wasn't well-informed. The main reason for trying to be clever about extent sizing when all you had in the temp tablespace was sorts was to avoid excessive overheads on allocation and deallocation of segments and their extents. (But, except for odd cases, this was probably a small fraction of the cost of using the space anyway).

The sort_area_size + 2, or sometimes sort_area_size * 2 +1 types of formula were all second guessing (incorrectly) the way Oracle used the memory in sort_area_size.

Now, with only one real segment being maintained by the instance, and extents begin held after use, there's no big worry about the overheads of allocating and de-allocating. Given, though, that you use the temporary tablespace for sorts, hashes, temporary tables and their indexes, and temporary LOBs, there is an argument for worrying about how many chunks you might need to have active at a time.

Sizing is required to be uniform, and the 1Mb is a good general purpose compromise between small sorts, tables and indexes when compared to LOBs. But if all you use lots of very smal temp tables you might drop to (say) 128Kb, and if all you use is "large" temp LOBs and sorts you might bump the unit size up to 4Mb or 8Mb (say) - but in general there's no great need to worry too much.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html