Vivek,
My guess is probably "yes", but I should be honest
that I really have not worked with an environment with
these parameters turned on and not having statistics
on the objects (both tables and indexes), as if I
recall correctly, these parameters came into being in
8.0.4/8.0.5. I have used the CBO in every system that
I have worked on since version 7.3.4 and have had no
need to use the RBO in the past 4 years. It may be an
interesting test though.
Cheers,
Gaja
- VIVEK_SHARMA <VIVEK_SHARMA_at_infy.com> wrote:
>
> Does Implementing this Parameter Need the Indexes to
> be in ANALYZED
> State ?
>
> > -----Original Message-----
> > From: Gaja Krishna Vaidyanatha
> [SMTP:oraperfman_at_yahoo.com]
> > Sent: Thursday, June 07, 2001 11:31 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: OPTIMIZER_INDEX_COST_ADJ &
> OPTIMIZER_INDEX_CACHING
> >
> > Dear Oracle-L Listers,
> >
> > After 7 months of "hibernation", it feels good to
> be
> > back. Thanks to many of you for your continued
> support
> > and encouragement over the past months. Some of
> the
> > interesting threads were forwarded to me in the
> past
> > few months, just so that I don't miss out on the
> fun.
> >
> > Talk about good timing and a fun topic, this post
> was
> > one of the first messages to pop into my Inbox,
> after
> > I rejoined the list today. Although, I don't have
> the
> > whole thread, here are some comments to the
> previous
> > posts.
> >
> > --- Greg Moore <sqlgreg_at_pacbell.net> wrote:
> > > > My goal was to strongly favor indexes and make
> CBO
> > > behave like RBO.
> > >
> >
> > > Mladen,
> > >
> > > I would have considered setting
> > > optimizer_goal=first_rows and would
> > > appreciate hearing your ideas on why you decided
> to
> > > set
> > > OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank
> you.
> > >
> > > - Greg
> > >
> > > Sorry if this is a duplicate ... the mail server
> > > bounced the original.
> > >
> > > --
> >
> > I am not so sure that having the CBO work like RBO
> is
> > always the best method across the board for all
> > applications. Depending on the nature of your
> > applications, it can produce mixed results. If the
> > application is "transactional in nature", then
> index
> > scans will be a more preferred method versus
> > applications that are more "data mart in nature",
> > where full table scans or fast-full index scans
> may be
> > preferred. In today's world, there are very few
> "true
> > OLTP" systems, most of them act like OLTP during
> the
> > day, and like DataMarts during the batch window.
> >
> > The primary rationale behind "not overdoing the
> use of
> > indexes" is that "indexes are not always optimal
> for a
> > SQL's execution plan". The yardstick you need to
> use
> > is "how many block visitations does my SQL have to
> > perform with indexes versus without indexes?". If
> the
> > block visitations are lower for a "full table
> scan",
> > then that is the better plan. Setting
> OPTIMIZER_MODE
> > to FIRST_ROWS does twist the optimizer's arm to
> use
> > indexes over a full table scan and this may wreak
> > havoc during your application's "batch window".
> >
> > OPTIMIZER_INDEX_COST_ADJ directly adjusts the cost
> of
> > using an index. The default value of 100 makes the
> > optimizer evaluate the cost of the index as
> normal,
> > and a value of 50 makes the optimizer evaluate the
> > cost to be half as expensive as normal. This
> parameter
> > encourages the use of all indexes, regardless of
> their
> > selectivity. It applies to index use in general.
> >
> > While OPTMIZER_MODE=FIRST_ROWS, will almost always
> > force the use of an index, the parameter
> > OPTIMIZER_INDEX_COST_ADJ "encourages" the
> optimizer to
> > use indexes. When set to a value of 30, you are
> > telling the optimizer, that the use of indexes is
> > actually approximately 1/3rd of the normal cost.
> I'd
> > rather use the latter over the former, as in my
> > experience with ERP applications, I have found the
> > latter is less "forceful" than the former,
> especially
> > during the batch window.
> >
> > Hope that helps,
> >
> > Gaja
> >
> > =====
> > Gaja Krishna Vaidyanatha
> > Director, Storage Management Products,
> > Quest Software, Inc.
> > Co-author - Oracle Performance Tuning 101
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Get personalized email addresses from Yahoo! Mail
> - only $35
> > a year! http://personal.mail.yahoo.com/
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: Gaja Krishna Vaidyanatha
> > INET: oraperfman_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).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: VIVEK_SHARMA
> INET: VIVEK_SHARMA_at_infy.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).
Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year!
http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gaja Krishna Vaidyanatha
INET: oraperfman_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).
Received on Thu Jun 07 2001 - 13:28:49 CDT
