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: Phil Singer <>
Date: Mon, 22 Jan 2007 20:07:13 -0500
Message-ID: <>

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

Phil Singer                         |   psinger1 at chartermi dot net
PhD, OCP, and All Around Good Guy   |   Do the Obvious to Reply
Received on Mon Jan 22 2007 - 19:07:13 CST

Original text of this message