Re: moving 10g execution plan to 11g using SPM

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-l