RE: Questions about SQL Plan Management
Date: Wed, 2 Jul 2014 19:49:42 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DFFD68_at_exmbx05.thus.corp>
As the old joke goes - it's not that simple.
Query transformation is NOT exclusively done before "the optimization phase", there's a continuous feedback loop between transformation and optimization; and the plans in the SPM do capture the transformations - though not necessarily in a way that is immediately visible to the programmer eye, sometimes the transformational hints are actually visible, sometimes they are implied by the outline() and outline_leaf() hints which show how query blocks have been combined.
I can guarantee that SPM is not foolproof as I have had examples where a query has generated an SQL baseline, but the baseline doesn't reproduce the execution plan when it is enabled - and that's on the same version, with the same statistics, within 30 seconds, and with a report that the baseline was used.
In principle I think there are two 'valid' reasons for failure to reproduce: (1) bugs, (2) upgrades which introduce a new transformation that has not been blocked by the previous SPM - which would allow the SPM to be applied and still produce a change in plan (this is probably why 12c captures the actual plan, rather than just the list of hints, to check if the plan has reproduced).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Iggy Fernandez [iggy_fernandez_at_hotmail.com] Sent: 02 July 2014 19:48
To: oracle-l_at_freelists.org
Subject: Questions about SQL Plan Management
Since cost-based query transformation and rewrites are done before the optimization phase and since the plans stored in SQL Plan Management do not capture the transformations and rewrites, can we assert that SQL Plan Management is not a foolproof way of guaranteeing query plan stability?
Also, is query plan stability guaranteed in the absence of transformation and rewrites; that is, in the absence of query transformation and rewrites, can we assert that the collection of hints stored in SQL Plan Management (or in a stored outline) are always sufficient to reproduce the original query plan in all cases?
Iggy
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 02 2014 - 21:49:42 CEST