A brief update: sys.dbms_system.set_bool_param_in_session does not seem to work for use_stored_outlines, at least not in 11.1.0.7 It is probably due to this not being an actual parameter. Here's the procedure used: declare n_sid number; n_serial# number; begin select s.sid, s.serial# into n_sid, n_serial# from v$session s where s.client_info = 'TUNING_SESSION'; sys.dbms_system.set_bool_param_in_session(n_sid,n_serial#,'use_stored_outlines',true); dbms_output.put_line('USE_STORED_OUTLINES set true for sid:serial ' || to_char(n_sid) || ':' || to_char(n_serial#) ); end; Tested by creating an outline, running the relevant sql in a session and examining the real plan via select * from TABLE( dbms_xplan.display_cursor(:sqlidvar) ) flush the buffer and sql cache. set the use_stored_outlines parameter to true for the session via dbms_system. run the queries again no change in the plan flush the buffer and sql cache now run 'alter session set use_stored_outlines=true' in the appropriate session. run the queries. Check the plans and find that they are now using the outline. So, there is apparently no way to set use_stored_outlines=true for an existing application session Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com On Mon, Mar 1, 2010 at 8:44 AM, Jared Still <jkstill@xxxxxxxxx> wrote: > For the time being at least, (and as it has been since 8i I would guess) > if you have need to do this dynamically, then the default category must > be used. > > > Jared Still > Certifiable Oracle DBA and Part Time Perl Evangelist > Oracle Blog: http://jkstill.blogspot.com > Home Page: http://jaredstill.com > > > > On Mon, Mar 1, 2010 at 8:30 AM, Tanel Poder <tanel@xxxxxxxxxx> wrote: > >> While the regular (KSP) parameters and query compilation environment >> parameters (QKSCE, also known as optimizer environment) are stored in shared >> pool, the "use_stored_outlines" one isn't. It's similar to the >> "tracefile_identifier" parameter in that sense, these parameters are stored >> in the private memory of the process. >> >> So, if another process wants to change it, you would need to use oradebug >> to send a signal to that process, that process would stop doing whatever it >> was doing and handle the signal - change the string value (and allocate more >> memory if needed) and cleanly resume to do whatever it was doing. >> >> It's doable but probably too much effort compared to changing fixed >> variables via oradebug. >> >> Tanel. >> >> >> On Mon, Mar 1, 2010 at 10:40 PM, Jared Still <jkstill@xxxxxxxxx> wrote: >> >>> On Mon, Mar 1, 2010 at 1:21 AM, Dunbar, Norman < >>> norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote: >>> >>>> Morning all, >>>> >>>> >> That string parameters can have "variable width." is >>>> >> actually, in my >>>> >> opinion, a strong enough reason for Oracle to not provide or >>>> >> not even >>>> >> attempt to provide a SET command for parameters in other sessions. >>>> >>>> I disagree! >>>> >>>> However in my defence, I claim ignorance of how Oracle does things >>>> internally, but I'm almost 100% certain plain C is used, so, to change a >>>> string parameter would be as [simple as] follows: >>>> >>>> >>> Thank you Norman, I to am of the opinion it could be done. >>> >>> Perhaps it has never been done because it has not been formally >>> requested? >>> >>> >>> Jared Still >>> Certifiable Oracle DBA and Part Time Perl Evangelist >>> Oracle Blog: http://jkstill.blogspot.com >>> Home Page: http://jaredstill.com >>> >>> >> >