Re: Questions about SQL Plan Management
Date: Thu, 3 Jul 2014 13:49:56 -0600
Message-Id: <1EFD4B5C-D8D5-4EB5-B728-74AE52F61D0F_at_centrexcc.com>
By going through the full optimization exercise the CBO makes sure that the conditions are such that it CAN come up with the same plan as in SPM. ONLY then does it check if it is "allowed" to use this plan.
Wolfgang Breitling
breitliw_at_centrexcc.com
On 2014-07-03, at 1:27 PM, Iggy Fernandez <iggy_fernandez_at_hotmail.com> wrote:
> Thanks for all the insights.
>
> If SPM stores the approved plan in the SPM baseline, then why are the transformation and optimization procedures invoked (the 10053 trace proves this) every time the query is hard-parsed? Why not simply reuse the approved plan; in other words, why not bypass the transformation and optimization procedures? (The same question might be asked about stored outlines.)
>
> This suggests that the SPM baseline does not contain all elements of the approved plan. If SPM baselines are basically stored outlines, then they don't contain access and filter predicates; this could cause the stability guarantee to fail. There may be other scenarios which lead to a failure of the stability guarantee.
>
> Iggy
>
> P.S. I could not figure out where SPM stores plan details.
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 03 2014 - 21:49:56 CEST