| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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.
> 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
-- 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 - 00:16:26 CDT
 |
 |