Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: how to evaluate optimizer_index_caching

RE: how to evaluate optimizer_index_caching

From: Polarski, Bernard <>
Date: Mon, 22 Jan 2007 15:41:40 +0100
Message-ID: <25D4919915CCF742A88EE3366D6D913D117721CF@mailserver1>

This is a very conservative approach where you state that old situation was good and you track what went wrong.
However when you speak of upgrade, customers expects 'goodies' and once you have cleaned what went wrong after the upgrade, you need to provide to 'goodies' which at this moment will at least be as good as before since it was not the list of stuff to clean. Hence we are back to 'how do I know if I am optimal'. I would even say that after a release upgrade (and I am preparing the init.ora of a 10gr2 from a 10gr1) the situation is even worse:
I would like to show better but I have no way to assert my starting own situation toward the optimal. In the 'air' we were, in the 'air' we remains and if boss ask 'how are we better now' the only response on the spot is 'we got a higher release and following arithmetic 10 > 9'. The way to assert that we are better is the phone ringing less or I am too pessimistic.

Bernard Polarski
Oracle DBA  

-----Original Message-----
From: Mark W. Farnham [] Sent: maandag 22 januari 2007 15:32
To: Polarski, Bernard;; Cc: Oracle
Subject: RE: how to evaluate optimizer_index_caching

Perhaps the reverse logic will help somewhat:

If you upgrade and all your plans go to hell in a hand cart, then it is worth a try to tweak index caching while you figure things out.

It would have been better to save outlines for the queries that do the preponderance of your load before the upgrade, use the outlines, not tweak "affect all parses and plans" parameters and thus make it more probable to easily find the newly bad plans, but I'm assuming you don't have
that time machine available.

On part b, the real data being sampled from the system will likely be superior in effect than any tweak you provide to the parameters with guesses
for plug number from some set value mined from a paper or posting having nothing to do with your actual case.

Overall the effect of the parameter is to favor or disfavor nested loop solutions.

Can you find your bad (worst) plans? Were they formerly good or better with
a previous release or statistics which generated nested loop plans?

Is it practical to revise the bad (worst) plans that produce a significant
load or user inconvenience without toggling a parameter that may snipe other
currently good plans and which definitely tells a semi-permanent lie to the
optimizer which you will ever after be reluctant to change because you cannot manage the side effects?

Specific recommendations on tweaking general parameters require specific situations to diagnose.

Finally, remember it is not the sheer quantity of plans but rather the resultant significant cost and/or user inconvenience that is important.

Instrumentation, profiling, and tracing can help you figure that out from a
dramatic subset of "a whole DB" if you don't already know the answer from
your knowledge of the database you are considering.

It was the experience of many application suite upgrades in the last few years that tweaks to the cache parameters were an effective bandaid, but clear experimental evidence has been presented (a lot from members of this
list server) that there is no magic general value for these parameters for
the general case.

I hope this helps,


-----Original Message-----
[]On Behalf Of Polarski, Bernard
Sent: Monday, January 22, 2007 8:24 AM
To:; Cc: Oracle
Subject: RE: how to evaluate optimizer_index_caching

Thanks for the resource. I read it and it is the most complete up to now I ever found. I noticed 2 recommandations :

  1. First one is an implicit opinion taken out of a diagram in the pdf:

"If most of the executions plan are good then do not Modify optimizer_index_caching/cost_adj"

First glance, sound wise words but ... it is a big problem to assert that an SQL plan is optimal and you have to do it on 'most' SQL? In practical, it is not feasible to study a whole DB just to respond to one question. But at least there is a price on the question.

b) "With system statistics, the default value is usually good"

Which reformulate point a: if you don't know, don't touch.

So we have a least an answer: study all the DB and if most of the SQL plan are not good then tweak the values of optimizer_inder_caching/cost_adj.
No surprising that there is so little resource on these parameters. When I google on it, I got 'optimizer_index_(xxx).. blabla ... favour index access blabla...' and that's it.

Bernard Polarski
Oracle DBA

-----Original Message-----
From: Gints Plivna []

2007/1/22, Stefan Knecht <>:
> My colleague, christian antognini held a good presentation at last
> miracle db forum in denmark about the "CBO configuration roadmap"
where he
> explains those as well -- google for it

Yeahh that's good article. I'v also recommended that for several


Received on Mon Jan 22 2007 - 08:41:40 CST

Original text of this message