Re: Setting sort_area_size for another session

In article <c4fbc557.0111271402.314dee4d_at_posting.google.com>, lundellj_at_saic.com says...
>
>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?

see
http://www.ixora.com.au/newsletter/2000_11.htm and consider pretty much every thing you see on that sight "truth".

> 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

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp