RE: Q about Oracle Optimizer

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Sat, 18 Aug 2001 09:00:46 -0700
Message-ID: <F001.00370280.20010818090022@fatcity.com>

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

Received on Sat Aug 18 2001 - 11:00:46 CDT