RE: changing optimizer_mode for a running session

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

• 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).