Re: Setting sort_area_size for another session

Hi Thomas, hi Jens,

I tried the following in 8.1.6.3.0 in NT 4.0:

1. Start a session with sqlplus testus/testus and enquire the sort_area_size by dbms_utility.get_parameter_value. I received 16,777,216 (= 16 MB, my init.ora value). I inquired the userenv ('sessionid') (result 515) for the current session.
2. Start another session as internal and issued

execute sys.dbms_system.set_int_param_in_session (10, 281, 'sort_area_size', 100000)

where I had the sid 10 and the serial# 281 by

select sid, serial# from v$session where audsid = 515 (= the above userenv ('sessionid')).

c) In the session started in a) I enquired the sort_area_size parameter again. It was changed to 100,000 as requested by dbms_system.

Martin

Thomas Kyte wrote:
>
> 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!
>
> But, you can try a logon trigger with:
>
> begin
> execute immediate 'alter session set sort_area_size = .....';
> end;
>
> and either do it "on schema" for each user you want to have this or code:
>
> if ( user in ('BOB', 'MARY', .... ) )
> then
> execute immediate ...
> end if;
>
> (of course the logon trigger and execute immediate are both part of Oracle8i
> release 8.1 and up, not in 8.0 and before but there isn't any version to work
> with here!)
>
> --
> 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
Received on Tue Dec 18 2001 - 07:49:45 CST