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: Mark W. Farnham <>
Date: Mon, 22 Jan 2007 12:04:59 -0500
Message-ID: <>

My apologies. From the subject identification I thought your were inquiring about how to evaluate optimizer_index_caching. Instead, it now appears that you want overall to evaluate whether your plans are optimal and whether a particular upgrade has brought you to an improved situation.

This is a much more complex question and is even farther from a canned answer. The usual trend of Oracle upgrades is improvement, but that does not mean every single job mix improves uniformly.

Consider, for starters, that we have no idea whether you are in the neighborhood of full utilization of your existing host server. Using more of the server's capacity may be nearly free or very expensive.

We have no idea of your service level requirements, whether your "before" situation was incompliance, nor whether your "after" situation is in compliance.

If you want a study to evaluate the net effect of an upgrade that is exactly what you need to do or commission.

Usually it is not cost effective to achieve and document optimal performance. Rather you need to know what the required service level is and how much it is worthwhile to spend to achieve it in whole or in part. If growth of demand is in play you probably need to chart whether the headroom amonst actual performance, hardware capacity limitations, and growth are headed toward a dysfunctional collision. Indeed getting farther forward in releases in a vaccuum of the detailed environment can only be measured as better as compared to the calendar dates of support levels for the releases in question.

Now it is possible to build a system of typical queries, inserts, updates, and deletes as service level recorders which you can use for proxies as to the overall health of an installation. The same basic research about service levels and hardware capacity together with dollar (or Euro) value is required. Typical (but by no means exhaustive) proxies are time to display initial application screens, time to book an order, time to return first rows of data, and time to run critical batch jobs.

So no, there is no magic document to give you the answer you are looking for as if Harry Potter flicked his wand.



-----Original Message-----
From: []On Behalf Of Polarski, Bernard
Sent: Monday, January 22, 2007 9:42 AM
To: Mark W. Farnham
Cc: Oracle
Subject: RE: how to evaluate optimizer_index_caching

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 - 11:04:59 CST

Original text of this message