Re: Questions about SQL Plan Management
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 2 Jul 2014 22:01:00 +0200
Hi
NLS_LANG also can disable SPM. I have captured 10g plan for a few queries
with ORDER BY and NLS_LANG set to SPANISH, the query with SPM enabled in
11.2.0.4 with NLS_LANG set to america and the plan was not reproduced, it
was because NLS_SORT changed from spanish to binary
On Wed, Jul 2, 2014 at 9:49 PM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
wrote:
>
>
> 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
> @jloracle
> ------------------------------
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
> behalf of Iggy Fernandez [iggy_fernandez@xxxxxxxxxxx]
> *Sent:* 02 July 2014 19:48
> *To:* oracle-l@xxxxxxxxxxxxx
> *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
>
>
Other related posts:
