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 220.127.116.11 -- 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