Re: moving 10g execution plan to 11g using SPM
Date: Wed, 5 Mar 2014 11:38:53 +0100
Message-ID: <CAJ2-Qb97kOrBjCbbJ+44XQVFpZ5E=NL_zD36FEtP2UhVD8Enhw_at_mail.gmail.com>
The only difference is only nls_territory and nls_language which changes NLS_SORT ok...... So this basically means I would need a SQL Plan Baseline for each diffrent language my user might be potentially using?
By the way, I am not sure if this is a SQLT problem, no matter what I do with NLS_* settings SQLT never picks up the SQL Plan Baseline...! Not sure if it runs alter session to change NLS_* settings somewhere in the code.
Thanks
On Wed, Mar 5, 2014 at 11:10 AM, Carlos Sierra <carlos.sierra.usa_at_gmail.com>wrote:
> Ls,
>
> Thanks for letting us know. There was no need to review SQLT XTRACT in
> this case. Just for future reference: if we had use SQLT XTRACT on both
> systems then SQLT COMPARE, the latter does compare among other things NLS
> parameters, so it would had detected this overseen difference.
>
> Regarding SPM. Keep in mind SPM stores CBO Hints but not the actual Plan,
> so if you apply the same set of Hints, and lets say nls_sort has a
> different value (binary versus something else), then you may get a
> different plan, and since the plan_hash_value is different than the one
> recorded on the SPM, then the Plan cannot be used.
>
> Cheers,
>
> Carlos Sierra
>
> blog: carlos-sierra.net
> twitter: _at_csierra_usa
>
> Life's Good!
>
> On Mar 5, 2014, at 4:00 AM, Ls Cheng <exriscer_at_gmail.com> wrote:
>
> > Hi
> >
> > After investigating a bit more......
> >
> > It was NOT DBMS_SQLPA problem, the problem is NLS_LANG is different in
> 11g, once I put same NLS_LANG as 10g the query works fine and picks up the
> SQL Plan Baseline.
> >
> > It looks like SQL Plan Baselines are associated to NLS_LANG settings?
> >
> >
> >
> >
> >
> > On Tue, Mar 4, 2014 at 3:49 PM, Ls Cheng <exriscer_at_gmail.com> wrote:
> > Found the problem, it seems that running from DBMS_SQLPA baseline cannot
> be used for some reason, when running the query manually the baseline is
> picked up!
> >
> >
> >
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 05 2014 - 11:38:53 CET