I wrote a blog article which aims mainly to show what optimizer parameter the CBO will use to reproduce aSPM baseline plan: the current environment parameters or the parameters used during the SPM plan baseline capture? Having tested in this blog article only the influence of the optimizer_mode (all_rows versus first_rows mode) I ended up with a 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 CBO parameters without testing; particularly that I have already seen an otn thread dealing with the inability to reproduce a SPM plan because of a change in the _optim_peek_user_binds hidden parameter. Then, a post on oracle list about the non reproducibility of a SPM baseline following an upgrade from 10gR2 to 11gR2 prompted me to investigate the influence the NLS_SORT parameter can have on the reproducibility of a SPM baseline plan. Below are my investigations and findings:
First, the model
CREATE TABLE t
(c1 VARCHAR2(64), c2 CHAR(15), d1 DATE);
INSERT INTO t
SELECT
mod(ABS(dbms_random.random),3)+ 1||chr(ascii('Y')) ,
dbms_random.string('L',dbms_random.value(1,5))||rownum ,
to_date(TO_CHAR(to_date('01/01/1980','dd/mm/yyyy'),'J') + TRUNC(dbms_random.value(1,11280)),'J')
FROM dual
CONNECT BY level <= 2e6;
ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (c1,c2) USING INDEX;
EXEC dbms_stats.gather_table_stats (USER, 't', CASCADE => true, method_opt => 'FOR ALL COLUMNS SIZE 1');
Second, the preliminaries
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; SQL> SELECT c1 FROM t GROUP BY c1 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; SQL> select plan_name from dba_sql_plan_baselines; PLAN_NAME ------------------------------ SQL_PLAN_90sg67694zwyj641607ca -- one SPM plan baseline
So far I have engineered a model against which I executed a query returning data in a certain order under the classical Binary NLS_SORT parameter. I added to that situation a SPM baseline plan so that any “resembling” query will use that SPM plan. That is plan stability. This SPM plan looks like:
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]
An index full scan with which the CBO has avoided the order by sort operation.
Now, for my “tranquility”, I will execute my query and check if the SPM plan is used or not.
SQL> SELECT c1
FROM t
GROUP BY c1
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
Great it is used.
Third, the issue presentation and discussion
What happens if, in my current environment, I change the NLS_SORT parameter?
SQL> alter session set nls_sort=french; -- I altered my current environment when compared to the SPM capture time environment SQL> SELECT c1 FROM t GROUP BY c1 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 | ------------------------------------------------------------------------------
See how my query is not using the SPM baseline plan anymore. It is using a new plan where the sort order by operation has not been eliminated by the CBO. If my query is not using the SPM plan this is because the CBO was not able to reproduce the stored Baseline plan because of nls_sort parameter change. If this means something it then means that when trying to reproduce the SPM plan the CBO uses the current nls_sort parameter.
The new CBO plan have been added to the SPM baseline for an eventual evolution
SQL> select plan_name from dba_sql_plan_baselines; PLAN_NAME ------------------------------ SQL_PLAN_90sg67694zwyj297df088 SQL_PLAN_90sg67694zwyj641607ca
If I alter again my current nls_sort parameter so that it will match the one stored against the baseline plan, then my query will be back to its initial use of the SPM plan
SQL> alter session set nls_sort=binary; SQL> SELECT c1 FROM t GROUP BY c1 ORDER BY c1 ASC NULLS LAST; SQL_ID 5hrfv0352fzdr, child number 0 ------------------------------------- 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
Bottom line: in contrast to the optimizer_mode parameter when it comes to NLS_SORT (and NLS_LANG) parameter, the CBO seems to use the current environment NLS_SORT value (and not the one that existed at the baseline time capture) to reproduce the stored SPM plan baseline.
Mohamed Houri’s Oracle Notes 