Re: how to evaluate optimizer_index_caching

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

a) 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.


Where I work, it is a corporate policy that all init.ora parameters (except for the SGA) must be set to their defaults. I've had two queries in 5 five years (both batch jobs, fortunately) where the default of zero was very, very wrong. Dead wrong. Makes a 3 minute update take 8 hours wrong. And no amount of hints could get the nested loop back.

Explain plan would should a nested loop.  But not the actual execution.

An alter session statement fixed it. I was so excited I tried it with all the batch jobs I had. Found out that 70% were unchanged, 15% had a modest improvement, and 15% had a modest unimprovement. Decided the time was not right to fight corporate. But I left my alter session in place.

Note:  The 5 years spanned progressive upgrades from 8.1.5 to 9.2.0.8

--
Phil Singer                         |   psinger1 at chartermi dot net
PhD, OCP, and All Around Good Guy   |   Do the Obvious to Reply
--
http://www.freelists.org/webpage/oracle-l


Other related posts: