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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
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: [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).
