Re: dbms_system - set a string parameter in another session?
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 18.104.22.168
It is probably due to this not being an actual parameter.
Here's the procedure used:
select s.sid, s.serial# into n_sid, n_serial#
from v$session s
where s.client_info = 'TUNING_SESSION';
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
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
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.
>> 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
>>> Jared Still
>>> Certifiable Oracle DBA and Part Time Perl Evangelist
>>> Oracle Blog: http://jkstill.blogspot.com
>>> Home Page: http://jaredstill.com