| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: changing optimizer_mode for a running session
Thank you.
I had done a DESC DBMS_SYSTEM and used SET_EV but never bothered to look for doc/notes on SET_BOOL_PARAM_IN_SESSION. Hemant
At 04:08 AM 12-06-03 -0800, you wrote:
Oh, my bad.. ;)
Here you go:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL> select sid, serial#, username from v$session;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
1 1
2 1
3 1
4 1
5 1
6 1
7 3757
8 3761
9 3761
10 3761
11 20 NORAD_USER
SID SERIAL# USERNAME
---------- ---------- ------------------------------
12 7213 SYS
17 532 GERRY
SQL> exec dbms_system.SET_BOOL_PARAM_IN_SESSION (17, 520, 'optmizer_mode',
TRUE);
PL/SQL procedure successfully completed.
Mark
-----Original Message-----
Hemant K Chitale
Sent: 12 June 2003 12:09
To: Multiple recipients of list ORACLE-L
I am talking of a scenario where another user
has already connected to the database and is running
a report.
I can use dbms_system.set_ev to get a 10046 trace
online [the "running session"] but can't change it's
optimizer_mode.
I do not want to have to stop the session, change and
restart it --- it could be a report to modify which
could take a few weeks in the approval cycle or
it could be coming from an application server / JSP which
has generated it's own SQL.
Changing optimizer_mode globally at the ALTER SYSTEM
level isn't what I am looking for either.
Hemant
--- Mark Leith <[EMAIL PROTECTED]> wrote:
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
> With the Partitioning option
> JServer Release 8.1.7.0.0 - Production
>
> SQL> alter session set optimizer_mode = rule;
>
> Session altered.
>
> SQL>
>
> SQL> alter session set optimizer_mode = choose;
>
> Session altered.
>
> SQL>
>
> HTH
>
> Mark
>
> ===================================================
> Mark Leith | T: +44 (0)1905 330 281
> Sales & Marketing | F: +44 (0)870 127 5283
> Cool Tools UK Ltd | E: [EMAIL PROTECTED]
> ===================================================
> http://www.cool-tools.co.uk
> Maximising throughput & performance
>
> -----Original Message-----
> Hemant K Chitale
> Sent: 12 June 2003 11:25
> To: Multiple recipients of list ORACLE-L
>
>
>
> Is there any way that we can change the optimizer_mode
> for a running session ? [Oracle 8.1.7]
>
> The session may have started with the instance-level
> optimizer_mode=RULE [Oracle Apps R11.0] but I
> want to change the optimizer_mode for subsequent SQLs
> executed by the session, without killing and
> restarting the session -- something like using
> DBMS_SYSTEM.SET_EV to set an event in a running session.
>
>
>
> Hemant K Chitale
> http://hkchital.tripod.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Hemant K Chitale
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting
> services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like
> subscribing).
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.487 / Virus Database: 286 - Release Date: 01/06/2003
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.487 / Virus Database: 286 - Release Date: 01/06/2003
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mark Leith
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting
> services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like
> subscribing).
>
Hemant K Chitale
http://hkchital.tripod.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.487 / Virus Database: 286 - Release Date: 01/06/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.487 / Virus Database: 286 - Release Date: 01/06/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Jun 12 2003 - 08:47:38 CDT
 |
 |