Create a Function-based Index with NLSSORT

Dealing with different NLS_SORT settings by creating a functional Index

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.

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'))

Hope it helps.