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