Re: Setting sort_area_size for another session

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9tubr004vs_at_drn.newsguy.com>...
> In article <c4fbc557.0111261028.115bbf7f_at_posting.google.com>, lundellj_at_saic.com
> says...
> >
> >I know I can set sort_area_size (and sort_area_retained_size) at the
> >instance level in init.ora and for "my" session using "alter session
> >set sort_area_size = xxx". But is it possible to set sort_area_size
> >for another session or for a specific user and/or user type? We have a
> >third party application that (due to bad SQL) needs a BIG sort area
> >but we can't afford to set that big sort area at the instance level.
> >
> >Thanks,
> >
> >-Jens

> Well, since sort_area_size is more of an upper bound then a fixed size of
> memory....
>
> and we only allocate sort memory when we sort (so unless they need it, they
> won't allocate it) ....
>
> ... if the other sessions don't need that much, they won't use that much!

I apologize for not mentioning that I'm using Oracle8i Enterprise Edition 8.1.6.1.0 with Solaris 2.7.

I've been manually executing the troublesome SQL (that I got from V$SQLTEXT) and increasing my session's sort_area_size does improve performance. But it's not until I also increase sort_area_retained_size significantly that performance really starts to improve (and sorts are completely done in memory as witnessed by V$SYSSTAT). I understand that increasing sort_area_size at the instance level is relatively harmless as long as I don't have many sorts at exactly the same time. But what about sort_area_retained_size, for how long is that memory really retained (my sources are not exactly clear on this)? After a sort, does the session retain sort_area_retained_size bytes for the lifetime of the session or is all the sorting memory deallocated (and hence available for other sessions) as soon as the fetch cycle for the query completes? In other words, is it harmless to significantly increase sort_area_retained_size at the instance level as long as my users (in the hundreds) don't perform sorts at exactly the same time?

And thanks everyone, the logon trigger trick worked just fine (but I'd rather set the parameters in init.ora once and for all).

-Jens Received on Tue Nov 27 2001 - 16:02:44 CST