RE: Q about Oracle Optimizer

Hi all,

Although it is a little late in this thread, keep in mind that 'STATISTICS=ESTIMATE' kicks in by default for export. This could also have affected the final outcome, i.e. CHOOSE with incorrect stats isn't exactly the right combination for the best performance ;-)

IMHO, this gotcha has caught out a lot of DBAs - even experienced ones...

John Kanagaraj

>-----Original Message-----
>From: Mohammad Rafiq [mailto:rafiq9857_at_hotmail.com]
>Sent: Saturday, August 18, 2001 10:00 AM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: Q about Oracle Optimizer
>
>
>Anita,
>You are 100% right for your explanation. I observed that
>behaviour just last
>week when we migrated our second production database from 7.3.4.5 to
>8.1.6.2 and my colleague forgot to define optimizer as rule in our new
>init.ora file and resultantly optimizer became CHOOSE and from
>very next day
>users started complaining about slowness. When I investigated
>and found
>problem with that, I defined it as RULE and rebounced database
>at day-end
>and problem resolved.
>
>MOHAMMAD RAFIQ
>
>
>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Date: Sat, 18 Aug 2001 07:16:20 -0800
>
>Mohammad,
>
>For this particular case you're probably correct.
>What I was attempting to explain, and not doing very
>well, was that the absence of statistics does NOT mean
>the RBO will ALWAYS be used. Many people don't
>realize this and assume that because they have
>OPTIMIZER_MODE = RULE that they will always use the
>RBO. Even if OPTIMIZER_MODE = RULE, the CBO will be
>used on queries involving partitioned tables, for
>example.
>
>It doesn't help that tkprof shows the optimizer_mode
>for the session, not the optimizer that was actually
>used. So it's quite common to have the output show
>RULE, while the explain plan itself shows a calculated
>cost, hash joins, parallel query paths, and/or other
>features that are used only by the CBO.
>
>HTH,
>
>-- Anita
>
>--- Mohammad Rafiq <rafiq9857_at_hotmail.com> wrote:
> > IMHO,Problem in this situation is that ,in 7.3.4 ,if
> > I am not wrong ,thier
> > optimizer must be RULE as default and after
> > migration if optimizer is not
> > explicitly set as RULE , default optimizer in 8.1.6
> > is CHOOSE. This is the
> > reason their objects never analyzed in 7.3.4 or now
> > in 8.1.6.
> > If optimizer is set explicitly as RULE in
> > initSID.ora will automatically
> > resolve their problem...
> > Besides just going to CHOOSE and analyzing objects
> > will not resolve all
> > performance issues as it depends on application
> > code, which was
> > tuned/written keeping in mind RULE based optimizer
> > and such codes must be
> > checked and modified to get the best result under
> > CHOOSE....
> >
> >
> > MOHAMMAD RAFIQ
> >
> >
> >
> > Reply-To: ORACLE-L_at_fatcity.com
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > Date: Sat, 18 Aug 2001 05:45:19 -0800
> >
> > That's true provided you're not using any of the new
> > features introduced in 8.0, and later versions, that
> > automatically cause the CBO to be used (e.g.
> > partitioning, degree or instances > 1, IOT, etc...)
> >
> > When in doubt, I always check metalink note:
> > 66484.1.
> >
> > HTH,
> >
> > -- Anita
> >
> > --- Jon Walthour <jonw_at_fuse.net> wrote:
> > > Volker:
> > >
> > > If optimizer_mode = 'choose', then the optimizer
> > > chooses between
> > > cost-based optimization and rule-based
> > optimization
> > > based on whether or
> > > not statistics are present. In your case, since
> > they
> > > aren't it's using
> > > rule-based.
> > >
> > > -----Original Message-----
> > > Volker
> > > Sent: Thursday, August 16, 2001 4:46 AM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Hi list,
> > >
> > > a quick question about oracle optimizer.
> > >
> > > a customer has database migrated from 7.3.4 to
> > 8.1.6
> > > on AIX. Data was
> > > transfered via full exp/imp. After migration the
> > > performance of the new
> > > database is very poor. I looked at the parameters
> > > and saw that no object
> > > is analyzed. So now me question:
> > >
> > > What does oracle optimizer do, if there are no
> > > statistics on all
> > > objects. Optimizer level is choose.
> > >
> > > Volker Schön
> > > E-Mail: mailto:v.schoen_at_inplan.de
> > > http://www.inplan.de
>
>
>__________________________________________________
>Do You Yahoo!?
>Make international calls for as low as $.04/minute with Yahoo!
>Messenger
>http://phonecard.yahoo.com/
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: A. Bardeen
> INET: abardeen1_at_yahoo.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (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).
>
>
>_________________________________________________________________
>Get your FREE download of MSN Explorer at
http://explorer.msn.com/intl.asp

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohammad Rafiq
  INET: rafiq9857_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).