LANGUAGE_MISMATCH - shared cursor

Oracle NLSSORT Performance: Fix Full Table Scans with a Function-Based Index

Oracle can implicitly apply NLSSORT() to predicates when linguistic comparison is enabled through settings such as NLS_COMP=LINGUISTIC and a non-binary NLS_SORT.

This can have an important performance consequence: an existing index on the original character column may no longer be usable for the transformed predicate, potentially resulting in a full table scan.

For example, a predicate may appear in the execution plan as:

NLSSORT(SESSION_GID, 'NLS_SORT=''TURKISH_AI''')
= NLSSORT(:B1, 'NLS_SORT=''TURKISH_AI''')

One solution is to create a matching function-based index:

CREATE INDEX idx_session_gid_nlssort
ON logon_logoff (
NLSSORT(session_gid, 'NLS_SORT=TURKISH_AI')
);

This article demonstrates a real production case where different NLS settings resulted in different execution plans and shows how a function-based NLSSORT index restored efficient index access.

NLSSORT generates a collation key that Oracle can use to compare and sort character values according to a specified linguistic collation.

For example:

NLSSORT(column_name, 'NLS_SORT=TURKISH_AI')

uses the TURKISH_AI collation, where _AI specifies an accent-insensitive comparison.

NLSSORT can appear explicitly in SQL or be applied as part of linguistic comparisons depending on the session’s NLS_COMP and NLS_SORT settings.

I’ve noticed that a SQL statement in one of our production databases is encountering performance fluctuations. I’ve been investigating to pinpoint the root cause of this issue. Interestingly, the query seems to be employing two distinct SQL plans, with one performing adequately and the other falling short of acceptable performance levels.

I have used query provided below to see how different plans perform.

select plan_hash_value
inst_id,
SUM (executions) as total_exec_count,
SUM (elapsed_time) as total_elapsed,
SUM (elapsed_time) / SUM (executions) as elapsed_perexec
FROM gv$sql
WHERE sql_id = '36av371yyctxf'
GROUP BY plan_hash_value, inst_id;
PLAN_HASH_VALUE INST_ID total_exec_count total_elapsed ELAPSED_PEREXEC
2495358432 1 65 768785522 11827469.5692308
1751229217 1 112444 41895314 372.588257265839
1751229217 2 36477 11051569 302.973627217151
2495358432 2 65 767930057 11814308.5692308

The query executes at an average of 300 milliseconds when utilizing the plan_hash_value – 1751229217. I’ve raised the question of whether it selects different plans based on the bind value, which might vary across data and could potentially be associated with Adaptive Cursor Sharing and bind peeking mechanisms. But when i checked gv$sql is_bind_aware column, it was flagged as ‘N’. Performance fluctuation was not related with ACS.

Query was a very simple one.

The provided execution plan utilizes an index scan.

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR('36av371yyctxf','ADVANCED');
SQL_ID 36av371yyctxf, child number 0
-------------------------------------
UPDATE LOGON_LOGOFF set LAST_ACT_TIME = :1 where SESSION_GID = :2
Plan hash value: 1751229217
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 3 (100)| |
| 1 | UPDATE | LOGON_LOGOFF | | | | |
|* 2 | INDEX UNIQUE SCAN| UI_LGNLGT_SSSND | 1 | 52 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - UPD$1 / LOGON_LOGOFF@UPD$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
ALL_ROWS
OUTLINE_LEAF(@"UPD$1")
INDEX(@"UPD$1" "LOGON_LOGOFF"@"UPD$1" ("LOGON_LOGOFF"."SESSION_GID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
2 - :2 (VARCHAR2(30), CSID=39): 'FkyxilHlWsFykUBUswVIRCJH6pxyjyDOot1tGUXp'
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SESSION_GID"=:2) ---> Index is filtered directly.
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (upd=3; cmp=2) "LOGON_LOGOFF".ROWID[ROWID,10], "SESSION_GID"[VARCHAR2,300], "LAST_ACT_TIME"[TIMESTAMP,11]
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[UPD$1]]></n><f><h><t><![CDATA[LOGON_LOGOFF]]></t><s><![CDATA[UPD$1]]></s></h></f></q>

Child number 16 was using Full Table Scan, I have detected filtering gv$sql with the plan_hash_value=2495358432. The execution plan utilizing from a full table scan, is as provided.

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR('36av371yyctxf',16,'ADVANCED');
SQL_ID 36av371yyctxf, child number 16
--------------------------------------
UPDATE LOGON_LOGOFF set LAST_ACT_TIME = :1 where SESSION_GID = :2
Plan hash value: 2495358432
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 14540 (100)| | | |
| 1 | UPDATE | LOGON_LOGOFF | | | | | | |
| 2 | PARTITION RANGE ALL| | 1 | 52 | 14540 (1)| 00:00:01 | 1 |1048575|
|* 3 | TABLE ACCESS FULL | LOGON_LOGOFF | 1 | 52 | 14540 (1)| 00:00:01 | 1 |1048575|
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
3 - UPD$1 / LOGON_LOGOFF@UPD$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
ALL_ROWS
OUTLINE_LEAF(@"UPD$1")
FULL(@"UPD$1" "LOGON_LOGOFF"@"UPD$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
2 - :2 (VARCHAR2(30), CSID=39): 'RMd05xarCfgez5Dbn4Wn50XcqaMG7ZsLeXj2TuRV'
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NLSSORT("SESSION_GID",'nls_sort=''TURKISH_AI''')=NLSSORT(:2,'nls_sort=''TURKISH_AI'''))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (upd=3; cmp=2; rowset=31) "LOGON_LOGOFF".ROWID[ROWID,10], "SESSION_GID"[VARCHAR2,300], "LAST_ACT_TIME"[TIMESTAMP,11]
3 - (rowset=31) "LOGON_LOGOFF".ROWID[ROWID,10], "SESSION_GID"[VARCHAR2,300], "LAST_ACT_TIME"[TIMESTAMP,11]
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[UPD$1]]></n><f><h><t><![CDATA[LOGON_LOGOFF]]></t><s><![CDATA[UPD$1]]></s></h></f></q>

We observed a discrepancy in the predicate information within the predicate information section of the execution plan choosing full table scan. Upon closer inspection, we found that when the query arrives with different NLS (National Language Support) values, it generates a separate child and follows a different plan. This separation into different child processes is caused by the mismatch in language settings (different NLS values).

Although the query is primarily executed with consistent NLS settings matching those of the database, a few executions occur with varying NLS settings. Upon contacting the application team, we learned that they are invoking different NLS settings for certain specific cases, which are not universally applicable to all sessions.

I have observed the same behavior using the following SQL statements.

SQL> alter session set NLS_SORT='TURKISH_AI' ;
SQL> alter session set NLS_COMP='LINGUISTIC' ;
SQL> alter session set NLS_LANGUAGE='TURKISH' ;
SQL> SELECT * FROM logon_logoff WHERE session_gid = 'R-kJbKW61tbHQKIreUKrMKDaKNF-iCCh_jotJxJJ';

Now i will create a functional index to fix the issue.

SQL> CREATE UNIQUE INDEX in_lgnlgff_nlssrtsssnd
ON logon_logoff (NLSSORT (SESSION_GID, 'NLS_SORT=TURKISH_AI'))
PARALLEL 32
ONLINE;
SQL> ALTER INDEX in_lgnlgff_nlssrtsssnd NOPARALLEL;

Now here is the results.

SQL_ID 9u3myqfb1s7zs, child number 1
-------------------------------------
SELECT * FROM logon_logoff WHERE session_id = 'R-kJbKW61tbHQKIreUKrMKDaKNF-iCCh_jotJxJJ'
Plan hash value: 837337298
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| LOGON_LOGOFF | 1 | 188 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX UNIQUE SCAN | IN_LGNLGT_NLSSRTSSSND | 1 | | 2 (0)| 00:00:01 | | | ----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LOGIN_LOGOUT"."SYS_NC00011$"=HEXTORAW('64464119467AF6F16E193762463C64287346645046231446552D3E1E1E3741
5A6E417D41410002002D02020202020101020202020202020202020202020202020202002D02020202005F0202020202020200'))
CriteriaBeforeAfter
PredicateNLSSORT(column)=NLSSORT(:bind)Same
Existing normal indexNot effectively used—
Access pathFull table scanIndex scan
IndexNormal column indexFunction-based NLSSORT index
PerformancePoorImproved

In short, consider an NLSSORT function-based index when linguistic comparison is required, NLSSORT() is present in the predicate, the existing index cannot efficiently support that predicate, and the execution plan confirms that this is causing a performance problem.

Different NLS settings can affect more than the access path. They can also prevent cursor sharing and create additional child cursors with LANGUAGE_MISMATCH.

I covered this behavior separately in Child Cursors Related with LANGUAGE_MISMATCH, including how to identify NLS-related cursor mismatches using V$SQL_SHARED_CURSOR.

Function-based indexes can also be useful for performance problems unrelated to linguistic sorting.

In Creating a Conditional Oracle Index Equivalent to a Partial Index in PostgreSQL, I demonstrate another production case where a function-based index was used to avoid an inefficient access path and significantly reduce buffer gets.

Hope it helps.


Discover More from Osman DİNÇ


Comments

Leave your comment