Skip to Main Content
  • Questions
  • Optimizer_Index_Caching and Optimizer_cost_adj

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Aman.

Asked: March 19, 2007 - 5:07 am UTC

Last updated: August 28, 2013 - 5:18 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Hi sir,
Its been a long time that I have posted any question here.Today I got lucky.I
want to understand the effect of optimizer_index_caching and optimizer_index_cost_adj parameters.According to your Book Effective Oracle by design, these two only make the optimizer "look" happy by changing the cost factor coming up in the plan but actual query performance is not effected.Than why we have to set them?What benefit they give when they actualyl dont make the query faster in terms of execution?How we can tune these parameters in a production system?
Thanks in advanace
With best regards
Aman....

and we said...

Ok, let me clarify, I said that these parameters affect the costing of the optimizer, they affect the numbers assigned to a query plan. If you set these, and this results in no change in plan, but does result in a lower cost - your query is NOT going to run any faster.

So, if the cost of a query with default settings for these two parameters was 1,000 - and you set these parameters and optimize the same query and get the same plan but the cost is now 500 - this query is NOT going to run two times faster.

This is different from other optimizer related parameters. For example, if you set db-file-multiblock-read-count to N and a query costs 1000, and then you set it to 2*N and the cost is 500 for the same exact plan - then that query may well actually run faster!!!. Because we are doing IO's using a bigger read.


Read this quote from the book:

<quote>
OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ Wrap-up

The setting of the OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ parameters will not make the plans run faster. It just affects which plan is chosen. It is important to remember that setting these parameter values does not affect how much of the index is actually cached or how expensive a single-block I/O truly is in relation to multiblock I/O.

note added by me right now: see that is important. It does not CHANGE how the query is processed at all. It does not cause more of the index to be cached, it lets you tell Oracle how much of the index you guess will be cached. Just changing these parameters will not cause the same plan to go faster (or slower). It will just change the COSTS associated with the plan. It might result in a DIFFERENT PLAN being chosen and that is where you would see performance differences!!!


Rather, this allows you to pass this information you have learned onto the CBO so it can make better decisions on your system. Also, it points out why just looking at the cost of a query plan in an attempt to determine which plan is going to be faster is an exercise in futility: Take two identical plans, with two different costs, which one is faster? Neither is.

The effect of adjusting these two parameters is that they have a profound and immediate impact on the CBO. They radically change the costing assigned to various steps. This, in turn, dramatically affects the plans generated. Therefore, you want to test thoroughly the effects of these parameters on your test system first! I¿ve seen systems go from nonfunctional to blazingly fast simply by adjusting these two knobs. Out of all of the Oracle initialization parameters, these two are most likely to be defaulted inappropriately for your system. Adjusting them is likely to greatly change your opinion of the CBO¿s abilities
</quote>

Rating

  (21 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Is there a process?

Bala, March 19, 2007 - 2:40 pm UTC

Is there a step by step process to derive the value for these two parameters for a given database/application?

Most often I read that 'it depends'.
Tom Kyte
March 19, 2007 - 3:45 pm UTC

sure, the process is in general:

let them default.


especially in 9ir2 and above with system statistics.
especially in 10gr1 and above

But.....

Aman Sharma, March 19, 2007 - 11:08 pm UTC

Hi sir,
Thanks alot for the reply.I understood that part that only the cost "looks" with these 2 parameters as they directly influence the costing step of optimizer.They dont actually make a query run faster.I understood it from your book.My doubt is that than why we should set these parameters?I mean to say that if I have to advice some one that these 2 parameters than what would I say as a definition that though these 2 parameters dont make the query run faster but still setting them can make a difference in the performance?I mean, its sounds like 2 opposite statements.We are saying at one point that these 2 parameters have no effect on the faster execution which is demanded by an end user, they only make the costing look smaller.On the other side we are saying that we should set them to have a better performance.How to relate these two terms in terms of the explanation?Or is it that I am missing something obvious here?

With best regards
Aman....
PS:Is unrelated to this thread sir.Is there any way out that one can have asktom threads in zip/tar file or can download it?Or the only possible way is to open threads week by week and save them?Can you consider this option to be given to have a download of asktom threads?Just a request and sorry its unrelated to the current thread.

Tom Kyte
March 20, 2007 - 7:49 am UTC

... My doubt is that than why we should set these parameters? ....

I tried to address that:

let them default.




These parameters affect the costing of various steps in a query plan.

If you change them, you become "not the same as everyone else, unusual".

It would be best to leave all optimizer related parameters alone - set as few as possible - none being best.

Changing them should be the last path taken in order to try to fix an otherwise un-fixable problem

Aman Sharma, March 20, 2007 - 12:26 pm UTC

Ok so let me summarize my understanding.Please correct me whereever I am wrong.
These parameters are given in order to let Optimizer chose a different plan when we have a poorly written query.They dont actualy make a query run faster or something like that.when set with the optimal values, they can result in a different plan which "may" run the query faster.These effect the costing of the plan which comes directly from the Buffer Cache percentages of both index and data blocks.
Optimizer_index_caching will tell hpw much index isin cache?And optmizer_index_cost_adj will tell how much data is in buffer cache!
Is it correct sir?Please add whatever I missed and also correct me.
Thanks and best regards
Aman....
Tom Kyte
March 20, 2007 - 12:46 pm UTC

... These parameters are given in order to let Optimizer chose a different plan when we have a poorly written query. ..


no, that is not accurate. These parameters may be used to tell the optimizer more information about the real current state of your system. However, we'd rather give system statistics and the optimizer (especially 10g) a chance before setting these.



Correct....

Aman Sharma, March 20, 2007 - 12:47 pm UTC

Agreed sir,thanks alot for the reply.Got it!
Best regards
Aman....

One small doubt again....

Aman Sharma, March 20, 2007 - 12:50 pm UTC

You said that they are used to tell the current state of the system.It means that how much data is in the buffer cache for indexes and data blocks, correct sir?But how do we know about this?I mean how do we know that this much data we have at the moment in our cache for both of these coz the values of thse parameters directly refer to it only?
Regards
Aman....
Tom Kyte
March 20, 2007 - 2:02 pm UTC

... But how do we know about this? ...

therein lies the rub. You don't - or you do - or you give it a better guess.

This is why I'll say it again: let it default.

Aman Sharma, March 20, 2007 - 3:03 pm UTC

I agree sir that we must let these values on default settings.But again, you mentioned in the book that 99% systems have the values set for these parameters as completely wrong.And in general outline, you have given the values also.My question was that how one can come up to a value for these parameters if there is a real need to change them from defgault to some value?
Thanks and best regards
Aman....
Tom Kyte
March 20, 2007 - 4:21 pm UTC

where did I say 99% of systems?!?!?

I'll just keep saying "let them default".

I'll be very repetitive

Especially in 10g and on

As a starting point

Chris Poole, March 20, 2007 - 8:09 pm UTC

Hello Tom,

For me this is a timely discussion. I've recently inherited some 9i systems that have these optimizer_index settings set to their default values and I believe I can get some payback by setting them more appropriately. As a starting point only, what do you think of the following code for deriving a value for the optimizer_index_caching parameter?
Thats assuming I understand the comment you made here:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12377460305583#43227084217203

select 
  c.t as index_block_cache_count,
  i.t as index_block_total_count,
  (c.t/i.t)*100 as start_optimizer_index_caching
from 
  (select sum(blocks) as t from dba_segments where segment_type = 'INDEX') i,
  (select count(*) as t from v$bh v, dba_objects o
  where v.objd = o.object_id and o.object_type = 'INDEX') c
/


For these systems the code is canned (3rd party) and I have no guidance as to what these should be set to.

I also understand that simply changing this value will undoubtedly change the number of index blocks in the cache (how very quantum) and this would have to be revisited. I also understand that the parameter will probably oscillate around a mean as I set it up and down in response to what I see. I also understand that yes, it would have to be run during a representative time of the business period.

So all I'm really looking for, is this an OK starting point and if not why not, is there faulty logic at work in my understanding or the code?

Many thanks,

Chris

Tom Kyte
March 20, 2007 - 8:16 pm UTC

.... (how very quantum) ....

laughing out loud. the observer observing changes that which they observe.

maybe something "simpler" can be applied:

.... or at least testing the two extremes:
¿ The default settings of OPTIMIZER_INDEX_CACHING = 0 and OPTIMIZER_INDEX_COST_ADJ = 100. These are typically appropriate for many data warehouse/reporting systems.
¿ The settings of OPTIMIZER_INDEX_CACHING = 90 and OPTIMIZER_INDEX_COST_ADJ = 25. These are typically appropriate for many transactional/OLTP systems.

system stats?

Mark Wooldridge, March 20, 2007 - 9:17 pm UTC

Do systems stats replace the optimizer_index_cost_adj and optimizer_index_cacheing parameters?

Do you agree with this from the performance tuning guide, "When Oracle gathers system statistics, it analyzes system activity in a specified time period (workload statistics) or simulates a workload (noworkload statistics). The statistics are collected using the DBMS_STATS.GATHER_SYSTEM_STATS procedure. Oracle Corporation highly recommends that you gather system statistics."?

Using either workload or noworkload values.
Tom Kyte
March 21, 2007 - 6:41 am UTC

yes, I concur that you want system statistics.

<quote src=Effective Oracle by design, the section right after I discussed the optimizer_index* parameters>


Use SYSTEM Statistics

Oracle9i introduced an alternative to OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING that is worth a serious look. The DBMS_STATS package was enhanced to collect a new type of statistic: system statistics. These statistics measure actual performance characteristics of your unique system. They measure your actual CPU and disk-performance characteristics using your actual workloads. They are not guesses. They are actual, measured observations of what has occurred in your system during the period of measurement. Depending on the type of work you actually perform, different costs will be associated with various operations, affecting the eventual query plans that result.

Every physical configuration will perform a little differently than any other configuration. That is why the perfect numbers for the OPTIMIZER_INDEX_* parameters cannot simply be stated. The best numbers rely on your systems fingerprint¿its unique characteristics. Hence, the ability for us to measure, collect, and then use actual performance characteristics of our systems can reduce or remove the need to guess at these figures.
</quote>

I see what you are saying but not quite what I asked!

Chris Poole, March 20, 2007 - 10:14 pm UTC

Hi Tom,

(I just knew you would get the whole quantum comment ;) I like to think we're on the same page, so to speak)

Well the systems are not pure OLTP nor are they DW!
One extreme has been 'tested', because the O.O.T.B. settings match that for DW. I say 'tested' because what I really mean is that the settings where just left as is.

So I'm trying to really identify a decent starting point. I'm not planning on doing this ad nauseum and scheduling jobs to pointlessly twiddle with memory settings just to make me look like some kind of super DBA.

I think I know why you reluctant to comment on such questions where values for 'tuning' are just 'magically' dug out of v$ (or better yet) obscure x$ views, I mean such scripts are rampant about the internet, but is it possible you could just humour me and comment on the SQL and the logic behind it? I promise to buy you a beer when (if) I ever get to meet you.

Ta,

Chris


PS what time zone are you in now? Is this your normal timezone? This has got to be for me one of the first times I have seen you taking questions while I've been Down Under. I will have to add your location to my time zone clock to give me a better chance of intercepting you!
Tom Kyte
March 21, 2007 - 6:45 am UTC

I'd start at two extremes IF I used them at all. system statistics can obviate the need to go with non-default parameters...

I'm on the east coast of the US...

Contradictory Messages ?

Hemant K Chitale, March 21, 2007 - 9:55 am UTC

I guess that putting the sentences "I've seen systems go from nonfunctional to blazingly fast simply by adjusting these two knobs. Out of all of the Oracle initialization parameters, these two are most likely to be defaulted inappropriately for your system. Adjusting them is likely to greatly change your opinion of the CBO's abilities." IS going to cause confusion when reading the rest of the thread. These sentences state that
a) the paramters CAN make a signficant difference
b) the defaults are LIKELY to be wrong
c) adjusting these paremeters MAY mean that you [the DBA] sees the CBO has not making the correct choices.

I guess the bottom line is that these paramters should be tried very late -- after exhausing other options like schema design, sql code and system statistics ?
Tom Kyte
March 21, 2007 - 11:22 am UTC

I keep saying... and said in the book as well....

system statistics...

and especially with 10g - let them default first and foremost.


System statistics or optimizer_index_*

MEHMOOD, March 21, 2007 - 2:36 pm UTC

Dear Tom

We got the very good results with settings of OPTIMIZER_INDEX_CACHING = 90 and OPTIMIZER_INDEX_COST_ADJ = 25.

I wanted to ask, should we try to go with System statistics and put the optimizer_index_* parameter with default values, we are using Oracle 9.2.0.7.

Thanks for your efforts and time.

Regards

Tom Kyte
March 21, 2007 - 7:36 pm UTC

if you are in production, and are happy as clams, I would stay that way.

However, when you upgrade, you should consider defaulting EVERYTHING you can and seeing what you see.

System statistics or optimizer_index_*

MEHMOOD, March 21, 2007 - 2:45 pm UTC

Dear Tom

We got the very good results with settings of OPTIMIZER_INDEX_CACHING = 90 and OPTIMIZER_INDEX_COST_ADJ = 25.

I wanted to ask, should we try to go with System statistics and put the optimizer_index_* parameter with default values, we are using Oracle 9.2.0.7.

Thanks for your efforts and time.

Regards

Aman...., March 25, 2007 - 8:43 am UTC

" "I've seen systems go from nonfunctional to blazingly fast simply by adjusting these two knobs. Out of all of the Oracle initialization parameters, these two are most likely to be defaulted inappropriately for your system. Adjusting them is likely to greatly change your opinion of the CBO's abilities." "
That's the same which have made me so much confused sir.I mean the difference that these parameters actually dont do anything but they effect how the optimizer thinks and behaves and the above said statement,I am not able to relate these two statements.I agree that one must not touch these parameters and let them defualt but when we change than actually what happens?That is the actual doubt.I am so sorry coz I am repeating the same thing again and again but unfortunately I am not clear.
Thanks and best regards
Aman....

Chris Carr, April 24, 2007 - 7:48 am UTC

Aman,

Changing these parameters will alter how the optimiser costs a query, one database I am investigating at the moment has the parameters set as follows.

optimizer_index_caching = 80
optimizer_index_cost_adj = 20

What these are telling the optimiser is to expect 80% of all required index blocks to be in the buffer cache and to reduce the cost of index operations to 20% of the value normally calculated.

In this case these settings have been in place for several years, and we are seeing serious performance problems when running reports. The optimiser is choosing plans that favour index access and nested loop joins over full table scans because the parameters are making these access paths appear cheaper than alternatives. Changing the parameters doesn't make a given execution plan any faster but may change which plan is choosen.

Changing the parameters for a single session runnning the reports on our "problem" database has shown using the default parameters produces a better plan and returns the results faster, so we are now testing the OLTP application to confirm if we can change the parameters at the system level or need to alter the reporting code to set the parameters for the session.

One of the things people forget when setting these types of parameters is you need to document why you have set them, and review the settings when you make changes to the application or the data volume or distribution changes. Otherwise you will come back in 6 months time and wonder why you don't have the default parameters set and not know the possible impact of any changes.
Tom Kyte
April 24, 2007 - 11:27 am UTC

... Changing the parameters doesn't make a given execution plan any faster but may change which plan is choosen. ....

exactly :) well said.


sarang, April 23, 2008 - 1:05 pm UTC

Well said!! optimizer_index_cost_adj and optimizer_index_caching helps to choose the differnet plan. I am testing one small query from huge report, which earlier took 18 minutes to run and now after setting this parameter to differnet values it is returning in 1 sec. I changed the parameters in such a way that optimizer will use the index rather than going for FTS and sorting for ORDER BY clause.
We are testing this full report again in clone environemnt and soon will be released in production.


"Optimizer_Index_Caching and Optimizer_cost_adj", on 10g RAC

Rasel, January 27, 2010 - 1:09 am UTC

Hi Tom,

We got very good result setting those values as 90 , 25 i.e. OLTP for ourfront-end. We have two node RAC where front-end in on node1 & rating system on node2. problem is node2 is not performing well with these values. I gather stat of all schemas in node1, should i gather stat of all schemas in node2 as well?
Tom Kyte
January 29, 2010 - 3:00 pm UTC

statistics are shared globally, you only gather them on one node. just make sure node 1 and node 2 have similar plans - one would expect they would. If not, you need to figure out "why" (different init.ora settings would be likely culprit)


problem is, these are not magic "fast=true" parameters. They change the costing model dramatically (making you different from the rest of the world) and it would not be recommended to set them if you don't have to - and you probably do not.

do you have good system statistics (not stats on system, but system statistics). IF you don't know what these are - read about them first.
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i41496

and for your oltp application, it might be very appropriate to optimize for first rows(n) (optimize for initial response time) versus the default of all rows. But do NOT (repeat DO NOT) set this in the init.ora - let the application issue an alter session or use a logon trigger for specific sessions.

OPTIMIZER_INDEX_CACHING & system stats

Alon Principal, March 16, 2010 - 11:30 am UTC

Hi Tom,

The default value of OPTIMIZER_INDEX_CACHING in 10gR2 is 0, which, if I undertand correct, means that optimizers assumes that no index blocks are cached. I believe this assumption is wrong for many queries mostly in OLTPs and I didn't find any system statistic that represents caching of index/data blocks in any way.

The result, as I can see it, is that the optimizer in 10gR2 calculates the execution plans based only on physical IO costs, which leads to many wrong plans.

1. Can you please explain which system statistic should guide the optimizer about my caching state that eliminates changing the default value of OPTIMIZER_INDEX_CACHING - as you suggest?

2. Is there any way (parameter?) that I can guide the optimizer about caching of DATA blocks?

* It would be great if you could refer to 10gR2 and 11iR2 in your answers?

Thanks you very much,
Alon.

Tom Kyte
March 16, 2010 - 2:30 pm UTC

... The result, as I can see it, is that the optimizer in 10gR2 calculates the
execution plans based only on physical IO costs, which leads to many wrong
plans.
...

that is not true if you have system statistics, there we compute the actual observed single block IO rates and multi block IO rates.


1) use system statistics, nothing fancy, just system statistics. Not stats on SYSTEM tables, but statistics on your *system*, it's cpu and IO capabilities.

2) no


System Statistics

Alon Principal, March 17, 2010 - 6:38 am UTC

Thanks for the answer but it's still not understood to me.

SREADTIM - it's the IO time of bringing single index or data block from the STORAGE. It doesn't count the time it takes to bring single blocks from the BUFFER CACHE.
I don't know any other system statistic about reading single blocks.

Then when the optimizer calculates the cost of reading rows through an index, it uses SREADTIM which means it assumes that EVERY block of the index is read from STORAGE and not from the buffer cache.
As I see it, this assumptions is wrong for almost any database.
Doesn't it cause sequential reads to be outweighed than scattered reads.

Isn't it obvious that usually there will be more than 0% of needed index blocks in the buffer chache?

Tom Kyte
March 17, 2010 - 8:43 am UTC

try it, use system statistics and see what you see.

I think you'll see what I'm saying - that it will use indexes when appropriate and use full scans when sensible.

Getting a block from the cache is faster than getting from disk - however, getting the block from cache is very expensive - especially with many concurrent users. In 10g, we use a mixed cpu and IO costing, not just a pure IO costing.

Before you attempt any magic "let us set this that and the other thing", just use system statistics. You might be surprised.

Avoiding Buffer Cache

Alon Principal, March 17, 2010 - 9:19 am UTC

I do use system statistics, I gather them during workload, I check its values and fidn them as expected according to my workloads and hardware.

But, still I get wrong plans - plans where FTS is unjustly preferred over INDEX SCANS - plans that cause major performance degradations.
I used 10053 on these SQLs, I understood the costs values and the formulas that used to calculate them but I think that the basic assumption of totally ignoring the possibility of blocks existance in the BUFFER CACHE during plans evaluation is totally wrong.

* Blocks that are read by index scans go to MRU and blocks that are read during full scans go to LRU.

* Blocks that are read by index scans read with single block read and blocks that are read by full scans read with multi-block reads that uses read-ahead algorithms.

It's impossible to use [SREADTIM*card] vs [MREADTIM/MBRC*card] (I simplified it on purpose just to show the general approach) when comparing costs of access through index and access through full table scan.
It's like comparing apples to oranges.

I remember that I saw in some book that in 10g there are tables: TAB_STATS$ and IND_STATS$ - is it something related to the issue? Does the optimizer use them in some way?


...
As long as I understand, CPU costing has nothing to do with the buffer cache, it just adds costing to IO costing according to how CPU intensive the evaluated operation and CPU power from system stats.


Tom Kyte
March 17, 2010 - 10:09 am UTC

* Blocks that are read by index scans go to MRU and blocks that are read during
full scans go to LRU.


not true, only true for large table full table scans, the table would have to be pretty large for this to be true (and it would make sense for large tables - otherwise they would flush the cache entirely). 'Small' tables are cached just like single block IO's are.

* Blocks that are read by index scans read with single block read and blocks
that are read by full scans read with multi-block reads that uses read-ahead
algorithms.


there are many types of scans on indexes - unique, range, full, fast full - fast full reads using multiblock IO.


As long as I understand, CPU costing has nothing to do with the buffer cache,
it just adds costing to IO costing according to how CPU intensive the evaluated
operation and CPU power from system stats.


correct, but not relevant. The CPU cost of doing things is taken into consideration - meaning - it is not just about IO.



But, still I get wrong plans - plans where FTS is unjustly preferred over INDEX
SCANS - plans that cause major performance degradations.


got a concrete example to look at for us? Probably more related to clustering factors rather than caching more than anything.

System Stats & Cache...

Alon Principal, March 21, 2010 - 1:28 pm UTC

I have many examples but it's more important for me to understand the basics.
I know that until 9i it was suggested to change OPTIMIZER_INDEX_CACHING for OLTPs as 0 was usually a wrong default value.

Since 9i we have system statistics that should save us from setting OPTIMIZER_INDEX_CACHING.

But I don't understand what exactly was changed since 8i that should make it work as the optimizer still uses phisycal reads comparison and without changing the default of OPTIMIZER_INDEX_CACHING it considers every read as physical read and the system statistics that gathered are:
1. CPUSPEEDNW
2. IOSEEKTIM
3. IOTFRSPEED
4. SREADTIM
5. MREADTIM
6. CPUSPEED
7. MBRC
8. MAXTHR
9. SLAVETHR

and any of the above isn't able to tell the optimizer which portion of physical reads they're actually logical reads.

I can see that system statistics are made the optimizer smarter on comparing physical IO of single block against physical IO of multiple blocks and it also considers CPU power for operations but regarding cached blocks and their effect I don't see anything that was changed since 8i...


Is this a valid parameter for 11G?

Amit, August 19, 2013 - 5:08 am UTC

Hi Tom,
As per note 396009.1 on Metalink, optimizer_index_caching & optimizer_index_cost_adj are there in Parameter Removal List for Oracle Database 11g Release 1.
So do you still suggest to set these two parameters in a 11G database, or I can simply remove these two parameters.
Please suggest.
Thanks
Amit
Tom Kyte
August 28, 2013 - 5:18 pm UTC

I suggest in current releases to let as many things default as humanly possible. These two in particular. I would remove them from init.ora's whenever possible.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.