20 March, 2007

Optimizer Index Cost Parameters

Frequently, I come across postings rejoicing about the benefits of OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING. However, in a recent response, Tom Kyte seems to be either a) Unimpressed b) Wanting to "put down" these parameters. Rather than making it clear that a performance benefit MAY arise IF the execution plan actually changes because of "tuning" these parameters, he just seems to say "ho hum .. the optimizer will perceive a difference in COST". He keeps repeating that COST would differ (and, I agree, that is why COST isn't always a reliable indicator of expected response time). But he forgets to mention that Performance also _may_ differ. However, he _does_ quote from his book "..I've seen systems go from nonfunctional to blazingly fast simply by adjusting these two knobs." which point isn't emphasised through the rest of his posting.
Am I saying that we should always try to tune these parameters ? No. Although I have used these parameter a few times with noticeable improvement in query response time, they don't always provide "faster" performance. Just to say, that when all other tuning is failing, these parameters are worth considering.


Chetan said...

Hi Hemant sir,

When I set optimizer_index_cot_adjust in my enviroment it had given a huge effect .Could you please explain them in brief.

Thanks in advance......

Chetan Patil

Hemant K Chitale said...

O_I_C_A adjusts the "cost" of an index. It makes an index more favourable over a full table scan.