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,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Polarski, Bernard
Sent: Monday, January 22, 2007 8:24 AM
To: gints.plivna_at_gmail.com; knecht.stefan_at_gmail.com Cc: Oracle Freelists.org
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 [mailto:gints.plivna_at_gmail.com]

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

http://www.trivadis.com/Images/CBOConfigurationRoadmap_tcm17-14317.pdf

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

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l