Re: moving 10g execution plan to 11g using SPM
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: Ls Cheng <exriscer@xxxxxxxxx>
- Date: Wed, 5 Mar 2014 11:51:07 +0100
Carlos,
Yes you are right for the NLS_SORT (and hence NLS_LANG).
I wrote a blog article which was mainly to show what optimizer parameter
the CBO will use to reproduce the SPM baseline plans. The current
environment parameters or the parameters used during the SPM plan baseline
capture. Having tested in this blog only the *optimizer_mode *parameter I
came out to the conclusion that the CBO will use the optimizer_mode
parameter stored during the SPM plan capture. I have also put a careful
warning that this is not a conclusion one can spread to other parameters
without testing particularly that I have already seen an otn thread dealing
with the inability of reproducing a SPM plan because of a change in the
use_bind_peeking hidden parameter. The current question gave me an idea to
test : NLS_SORT parameter. And here are my tests and conclusion
SQL> select * from dba_sql_plan_baselines;
no rows selected -- no baseline yet
SQL> show parameter nls_sort
NAME TYPE VALUE
------------------------------------ ----------- --------------
nls_sort string BINARY
SQL> alter session set optimizer_capture_sql_plan_baselines = TRUE;
Session altered.
SQL> SELECT c1
2 FROM t
3 GROUP BY c1
4 ORDER BY c1 ASC NULLS LAST;
C1
------
1Y
2Y
3Y
SQL> /
C1
------
1Y
2Y
3Y
SQL> alter session set optimizer_capture_sql_plan_baselines = FALSE;
Session altered.
SQL> select plan_name from dba_sql_plan_baselines;
PLAN_NAME
------------------------------
SQL_PLAN_90sg67694zwyj641607ca -- on SPM plan baseline
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name
=> 'SQL_PLAN_90sg67694zwyj641607ca', format => 'ADVANCED'));
--------------------------------------------------------------------------------
SQL handle: SQL_9061e639924ff3d1
SQL text: SELECT c1 FROM t GROUP BY c1 ORDER BY c1 ASC NULLS
LAST
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_90sg67694zwyj641607ca Plan id: 1679165386
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Outline Data from SMB:
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "T"@"SEL$1" ("T"."C1" "T"."C2"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
--------------------------------------------------------------------------------
Plan hash value: 2111031280
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 9 | 2069 (5)| 00:00:06
|
| 1 | SORT GROUP BY NOSORT| | 3 | 9 | 2069 (5)| 00:00:06
|
| 2 | INDEX FULL SCAN | T_PK | 2000K| 5859K| 2069 (5)| 00:00:06
|
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "T"@"SEL$1" ("T"."C1" "T"."C2"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "C1"[VARCHAR2,256]
2 - "C1"[VARCHAR2,256]
I have a simple query using an order by under *nls_sort* parameter value
set to binary. This sql query is constrained by a SPM plan baseline as
shown below
SQL> SELECT c1
2 FROM t
3 GROUP BY c1
4 ORDER BY c1 ASC NULLS LAST;
C1
----------------------------------------------------------------
1Y
2Y
3Y
SQL_ID 28dazsm20sbw6, child number 2
-------------------------------------
SELECT c1 FROM t GROUP BY c1 ORDER BY c1 ASC NULLS LAST
Plan hash value: 2111031280
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2069 (100)|
|
| 1 | SORT GROUP BY NOSORT| | 3 | 9 | 2069 (5)| 00:00:06
|
| 2 | INDEX FULL SCAN | T_PK | 2000K| 5859K| 2069 (5)| 00:00:06
|
-----------------------------------------------------------------------------
Note
-----
- SQL plan baseline SQL_PLAN_90sg67694zwyj641607ca used for this
statement
However when I change the NLS_SORT parameter the SPM baseline is not
anymore reproducible as shown below
SQL> alter session set nls_sort=french;
Session altered.
SQL> SELECT c1
2 FROM t
3 GROUP BY c1
4 ORDER BY c1 ASC NULLS LAST;
C1
-------------------------
1Y
2Y
3Y
SQL_ID 28dazsm20sbw6, child number 2
-------------------------------------
SELECT c1 FROM t GROUP BY c1 ORDER BY c1 ASC NULLS LAST
Plan hash value: 1760210272
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2451
(100)| |
| 1 | SORT ORDER BY | | 3 | 9 | 2451 (20)|
00:00:07 |
| 2 | SORT GROUP BY NOSORT| | 3 | 9 | 2451 (20)|
00:00:07 |
| 3 | INDEX FULL SCAN | T_PK | 2000K| 5859K| 2069 (5)|
00:00:06 |
------------------------------------------------------------------------------
And I have a new plan added to the SPM baseline for eventual evolution
SQL> select plan_name from dba_sql_plan_baselines;
PLAN_NAME
------------------------------
SQL_PLAN_90sg67694zwyj297df088
SQL_PLAN_90sg67694zwyj641607ca
*So, in contrast to the optimizer_mode parameter when it comes to NLS_SORT
(and NLS_LANG) the SPM plan baselines seems to use the current
NLS_SORT(NLS_LANG) parameter to reproduce the SPM plan and not the one used
during the SPM plan capture*
Best regards
Mohamed Houri
www.hourim.wordpress.com
2014-03-05 11:38 GMT+01:00 Ls Cheng <exriscer@xxxxxxxxx>:
> 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@xxxxxxxxx> 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: @csierra_usa
>>
>> Life's Good!
>>
>> On Mar 5, 2014, at 4:00 AM, Ls Cheng <exriscer@xxxxxxxxx> 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@xxxxxxxxx> 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!
>> >
>> >
>> >
>>
>>
>
--
Bien Respectueusement
Mohamed Houri
Other related posts:
