RE: changing optimizer_mode for a running session

Today is a bad day.

I'm seeing lots of wrong advice, or maybe everyone is in a humorous mood :-)

Regards
Naveen

> -----Original Message-----
> From: Mark Leith [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 12, 2003 5:39 PM
> To: Multiple recipients of list ORACLE-L
> Subject: 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).
>

DISCLAIMER:
This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Naveen Nahata
  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).