Monday, November 16, 2009

Changing NLS session parameters changes the plan

# Developer complained that performance was slow after setting NLS_SORT=CZECH_AI
# Confirmed that it changed the plan to run full table scans.

SQL> select name from emp where name = 'a';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2061206800

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMP_NAME | 1 | 12 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("NAME"=U'a')

Note
-----
- dynamic sampling used for this statement (level=2)

SQL> alter session set NLS_COMP=LINGUISTIC;

Session altered.

SQL> alter session set NLS_SORT=CZECH_AI;

Session altered.

SQL> select name from emp where name = 'a';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 12 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NLSSORT("NAME",'nls_sort=''CZECH_AI''')=HEXTORAW('14000100 ') )

Note
-----
- dynamic sampling used for this statement (level=2)