Re: how to evaluate optimizer_index_caching

  • From: Phil Singer <psinger1@xxxxxxxxxxxxx>
  • To: "Oracle" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 23 Jan 2007 20:06:59 -0500

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

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

Other related posts: