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