RE: In what circumstances might optimizer not choose the lowest cost?
Date: Tue, 20 Jan 2015 08:44:48 -0800
Message-ID: <BLU179-W34ABEE52298FE1DEACAB2DEB4B0_at_phx.gbl>
And, since costs are estimates, it's a moot point anyway The optimizer may select a plan with a lower costA plan with a lower cost may be more expensive than a plan with a higher costThe optimal plan (if is possible to find it) for one set of bind variables may not be optimal for another set of bind variables In general, the odds are stacked against us. But I'm preaching to the choir.
Iggy
From: iggy_fernandez_at_hotmail.com
To: kibeha_at_gmail.com; oracle-l_at_freelists.org Subject: RE: In what circumstances might optimizer not choose the lowest cost? Date: Tue, 20 Jan 2015 08:25:34 -0800
RE: Are there known circumstances where the optimizer does NOT choose the lowest cost, even though same query with a hint CAN produce a plan with a lower cost?
Obviously, yes, because otherwise the Oracle optimizer would be perfect. I would even say that we should expect it to happen a lot because the search space is large and the optimizer prunes it aggressively for obvious reasons. "No optimizer is perfect and directives such as Oracle’s hints provide the simplest workaround in situations in which the optimizer has chosen a suboptimal plan. Hints are useful tools not just to remedy an occasional suboptimal plan, but also for users who want to experiment with access paths, or simply have full control over the execution of a query." Iggy
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 20 2015 - 17:44:48 CET