Mohamed Houri’s Oracle Notes

March 6, 2014

NLS_SORT and SPM reproducibility

Filed under: Oracle — hourim @ 9:40 am

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.

About these ads

2 Comments »

  1. Reblogged this on Sutoprise Avenue, A SutoCom Source.

    Comment by SutoCom — March 7, 2014 @ 12:23 pm | Reply

  2. […] the other one for a NLS parameter (nls_sort) […]

    Pingback by SPM reproducibility: changing the _optimizer_skip_scan_enabled value | Mohamed Houri’s Oracle Notes — March 17, 2014 @ 9:36 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Database 11g

Oracle Database

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Follow

Get every new post delivered to your Inbox.

Join 102 other followers

%d bloggers like this: