| 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
What makes you think that the optimizer_mode is set to anything from your
example...??
Regis
-----Original Message-----
Sent: Thursday, June 12, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L
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
13 rows selected.
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
> 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). ********************************************************************* This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ********************************************************************** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regis Biassala 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:22:10 CDT
 |
 |