Temp tablespace Initial extent question

Hello,

I'm wondering what is the penalty of having an initial extent in a temp tablespace that is set too large? We have an app that connects to oracle as a single username. The application performs queries against small tables as well as against very large tables (2+ million rows). Performance is slow against the large tables due to the order by clause in the queries (which are needed). When I analyze the performance of these queries, I'm finding that oracle is spending 99% of it's time, just sorting the data.

There is no problem whatsoever with performance of queries against the smaller tables.

I've got my sort_area_size/retained size set as high as I can given the available memory on this box. Right now sort_area_size is about 2MB and my temp tablespace is configured accordingly with it's extent sizes based on the sort_area_size.

I'm considering either recreating my temp tablespace or creating a new temp tablespace with a larger initial/next extent, maybe 5 or 10 times the sort area size? But I'm wondering that if I do this, I may indeed improve the performance of larger queries...but have I degraded the performance of the smaller queries? Is there any statistic or utility I might use to quantify this? I'm trying to find an optimal balance.

Thanks for any advice.
Gavin Received on Sat Dec 01 2001 - 16:29:03 CST