RE: OPTIMIZER_INDEX_COST_ADJ & OPTIMIZER_INDEX_CACHING

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