Is your SORT_AREA_SIZE sufficiently large, and in what condition are your
indices?
"gdas" <gdas1_at_yahoo.com> wrote in message
news:7a4ed455.0112011429.1cd1e387_at_posting.google.com...
> 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:55:09 CST
