Re: dbms_system - set a string parameter in another session?

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_at_gmail.com> 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_at_poderc.com> 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_at_gmail.com> wrote:
>>
>>> On Mon, Mar 1, 2010 at 1:21 AM, Dunbar, Norman <
>>> norman.dunbar_at_environment-agency.gov.uk> 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
>>>
>>>
>>
>

--
http://www.freelists.org/webpage/oracle-l