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

  • From: Jared Still <jkstill@xxxxxxxxx>
  • To: Tanel Poder <tanel@xxxxxxxxxx>
  • Date: Wed, 3 Mar 2010 15:36:57 -0800

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
>>>
>>>
>>
>

Other related posts: