Re: Temp tablespace Initial extent question

Initial (and next) extent size should be a multiple of the sort_area_size, plus 1 block for housekeeping (unless you are using locally managed temporary tablespaces, in which case you forget the extra block... and because temporary tablespace has undergone so many changes in different versions, it would have helped knowing what version you are using!).

It should not impact your smaller sorts if you opt for a large multiple: it means they swap down several times into the same extent, but that should actually improve things slightly, rather than degrade it.

However, be aware that sort_area_size can be changed dynamically with an alter session command, so there is scope for a user, about to run one of your big queries, to have his/her sort_area_size increased to a large amount before the actual query is submitted. At the end of the query, the application sets it back to a much lower figure. It's poor design, I think, to have everybody sitting there with the same sort_area_size regardless of what they are doing.

Interestingly, 9i takes the same view: in that version, you can get the system to automatically grow and shrink the working area of the PGA, depending on what the User is actually doing with it. You should adopt the same approach manually in earlier versions, too.

Regards
HJR

--
Resources for Oracle: http://www.hjrdba.com
===============================


"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