One of my customers experienced a weird behavior – thousands of child cursors with LANGUAGE_MISMATCH
SQL> ;
1 DECLARE
2 v_count number;
3 v_sql varchar2(500);
4 v_sql_id varchar2(30) := '&sql_id';
5 BEGIN
6 v_sql_id := lower(v_sql_id);
7 dbms_output.put_line(chr(13)||chr(10));
8 dbms_output.put_line('sql_id: '||v_sql_id);
9 dbms_output.put_line('------------------------');
10 FOR c1 in
11 (select column_name
12 from dba_tab_columns
13 where table_name ='V_$SQL_SHARED_CURSOR'
14 and column_name not in ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS', 'CHILD_NUMBER', 'REASON', 'CON_ID')
15 order by column_id)
16 LOOP
17 v_sql := 'select count(*) from V_$SQL_SHARED_CURSOR
18 where sql_id='||''''||v_sql_id||''''||'
19 and '||c1.column_name||'='||''''||'Y'||'''';
20 execute immediate v_sql into v_count;
21 IF v_count > 0
22 THEN
23 dbms_output.put_line(' - '||rpad(c1.column_name,30)||' count: '||v_count);
24 END IF;
25 END LOOP;
26* END;
/
sql_id: 8bddhjmq5d9uc
------------------------
- BIND_MISMATCH count: 156
- LANGUAGE_MISMATCH count: 2599
- USE_FEEDBACK_STATS count: 75When I looked closer, into V$SQL_SHARED_CURSOR.REASON I saw something puzzling:
<ChildNode><ChildNumber>7</ChildNumber><ID>44</ID><reason>NLS Settings(2)</reaso
n><size>2x568</size><NLS_CURRENCY>'zl'->'zl'</NLS_CURRENCY><NLS_DUAL_CURRENCY>'z
l'->'zl'</NLS_DUAL_CURRENCY></ChildNode><ChildNode><ChildNumber>7</ChildNumber><
ID>39</ID><reason>Bind mismatch(8)</reason><size>4x8</size><bind_position>45</bi
nd_position><original_oacflg>19</original_oacflg><original_oacdty>1</original_oa
cdty><new_oacdty>2</new_oacdty></ChildNode>While this part is normal:
<original_oacflg>19</original_oacflg><original_oacdty>1</original_oa
cdty><new_oacdty>2</new_oacdty>Because it means that data type of bind variable changes, the following one makes no sense:
<ChildNode><ChildNumber>7</ChildNumber><ID>44</ID><reason>NLS Settings(2)</reaso
n><size>2x568</size><NLS_CURRENCY>'zl'->'zl'</NLS_CURRENCY><NLS_DUAL_CURRENCY>'z
l'->'zl'</NLS_DUAL_CURRENCY></ChildNode>It looks like something has changed ‘zl’ into…. ‘zl’. Let’s try to simulate this kind of behavior in my database. I created a simple script:
alter session set container=rick1;
alter system flush shared_pool;
select * from v$version;
select last_name from hr.employees where last_name like 'Ki%';
alter session set nls_territory=poland;
select last_name from hr.employees where last_name like 'Ki%';
select reason
from v$sql_shared_cursor
where sql_id=(select sql_id
from v$sql
where sql_text like 'select last_name from hr.employees where last_name like%'
and rownum=1)
/My database NLS settings are as follows:
SQL> select * from nls_session_parameters;
PARAMETER VALUE
-------------------------------------------------- --------------------------------------------------
NLS_LANGUAGE POLISH
NLS_TERRITORY POLAND
NLS_CURRENCY zl
NLS_ISO_CURRENCY POLAND
NLS_NUMERIC_CHARACTERS ,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT RR/MM/DD
NLS_DATE_LANGUAGE POLISH
NLS_SORT POLISH
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT RR/MM/DD HH24:MI:SSXFF
PARAMETER VALUE
-------------------------------------------------- --------------------------------------------------
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT RR/MM/DD HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY zl
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
Wybrano wierszy: 17.So I have pure polish NLS settings. In the script above, I’m setting NLS_TERRITORY to POLAND, so in theory I make no change at session level, compared to database level settings.
But when I run my test, I’m getting a new child with the following REASON:
SQL_ID CHILD_NUMBER
------------- ------------
8u7kzkuya3qfn 0
8u7kzkuya3qfn 1
<ChildNode><ChildNumber>0</ChildNumber><ID>44</ID><reason>NLS Settings(2)</reaso
n><size>2x568</size><NLS_CURRENCY>'zl'->'zl'</NLS_CURRENCY><NLS_DUAL_CURRENCY>'z
l'->'zl'</NLS_DUAL_CURRENCY></ChildNode>The question is: WTF?!
Let’s try to figure this out!
We will investigate 2 functions:
- kksIsNLSEqual – function responsible for comparing NLS when parsing child cursor
- lxhlmod – function responsible for changing NLS settings in session after ALTER SESSION command
Let’s create breakpoints in GDB!
(gdb) b lxhlmod
Breakpoint 1 at 0xe4e4840
(gdb) b kksIsNLSEqual
Breakpoint 2 at 0xc4f9a88
(gdb) c
Continuing.I will run my script again with those breakpoints now.
After stopping at
Breakpoint 1, 0x000000000e4e4840 in lxhlmod ()We can investigate input parameters to this function. I learned that the first parameter (on ARM it is register $X0) contains pointer to the NLS vector that will be changed by the function. So I will put the address of NLS vector into GDB variable to compare vectors after the function ends.
(gdb) set $vec = $x0Let’s check the current NLS_CURRENCY vector value (after a few tests I learned that NLS_CURRENCY is at offset 160, so 0xa0).
(gdb) x/1bs $vec+0xa0
0xffff83573190: "zl"We can see that it is equal to the one reported by nls_session_parameters.
But what will happen when I check it after ALTER SESSION SET NLS_TERRITORY=POLAND?
(gdb) x/1bs $vec+0xa0
0xffff83573190: "zł"It used to be "zl" and now it is "zł"! nls_session_parameters shows not such difference tho!
SQL> ;
1* select * from nls_Session_parameters
SQL> /
PARAMETER VALUE
-------------------------------------------------- --------------------------------------------------
NLS_LANGUAGE POLISH
NLS_TERRITORY POLAND
NLS_CURRENCY zl
NLS_ISO_CURRENCY POLAND
NLS_NUMERIC_CHARACTERS ,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT RR/MM/DD
NLS_DATE_LANGUAGE POLISH
NLS_SORT POLISH
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT RR/MM/DD HH24:MI:SSXFF
PARAMETER VALUE
-------------------------------------------------- --------------------------------------------------
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT RR/MM/DD HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY zl
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
Wybrano wierszy: 17.The server-side value is zł, but SQL*Plus converts the returned text to its declared US7ASCII client character set. Because ł cannot be represented in US7ASCII, the displayed value becomes l. We will cover this later in more detail. Let’s check what kksIsNLSEqual is seeing binary:
After stopping at this function we can see the whole mechanism that is qualifying cursor for shariness:
Breakpoint 2, 0x000000000c4f9a88 in kksIsNLSEqual ()
(gdb) bt
#0 0x000000000c4f9a88 in kksIsNLSEqual ()
#1 0x000000000c53e554 in kkscscid_nls_eval ()
#2 0x000000000c536520 in kkscsCheckCriteria ()
#3 0x000000000c535668 in kkscsCheckCursor ()
#4 0x000000000c53457c in kkscsSearchChildList ()We read it bottom-up:
- kkscsSearchChildList – search child cursor list
- kkscsCheckCursor – check the cursor
- kkscsCheckCriteria – validate criteria for cursor sharing
- kkscscid_nls_eval – evaluate NLS settings in the session vs. nls settings in the cursor
- kksIsNLSEqual – check if NLS vectors are the same
A quick check into kksIsNLSEqual reveals how the function is comparing NLS parameters:
0x000000000c4f9b64 <+232>: bl 0x8d2f408 <__memcmp@@GLIBC_2.17_veneer>So at offset 232, there is memcmp. Let’s top at this point.
(gdb) b *(kksIsNLSEqual+232)
Breakpoint 3 at 0xc4f9b64Now all variables for memcmp should be prepared, and we can try to investigate them:
(gdb) set $new_nls=$x0
(gdb) set $prev_nls=$x1
(gdb) x/1bs $new_nls+0xa0
0xffff83573190: "zł"
(gdb) x/1bs $prev_nls+0xa0
0x10857b870: "zl"There it is! Binary, NLS settings are not the same!
So when and how "zl" becomes "zł" or the other way around?!
I did the same test from SQLDeveloper and REASON was displayed properly:
<ChildNode><ChildNumber>0</ChildNumber><ID>44</ID><reason>NLS Settings(2)</reason><size>2x568</size><NLS_CURRENCY>'zl'->'zł'</NLS_CURRENCY><NLS_DUAL_CURRENCY>'zl'->'zł'</NLS_DUAL_CURRENCY></ChildNode> So is it only SQL*Plus problem? It seems so, because from Python I see correct values!
>>> for row in cursor.execute('select * from nls_session_parameters'):
... print(row)
...
('NLS_LANGUAGE', 'POLISH')
('NLS_TERRITORY', 'POLAND')
('NLS_CURRENCY', 'zł')
('NLS_ISO_CURRENCY', 'POLAND')
('NLS_NUMERIC_CHARACTERS', ', ')
('NLS_CALENDAR', 'GREGORIAN')
('NLS_DATE_FORMAT', 'RR/MM/DD')
('NLS_DATE_LANGUAGE', 'POLISH')
('NLS_SORT', 'POLISH')
('NLS_TIME_FORMAT', 'HH24:MI:SSXFF')
('NLS_TIMESTAMP_FORMAT', 'RR/MM/DD HH24:MI:SSXFF')
('NLS_TIME_TZ_FORMAT', 'HH24:MI:SSXFF TZR')
('NLS_TIMESTAMP_TZ_FORMAT', 'RR/MM/DD HH24:MI:SSXFF TZR')
('NLS_DUAL_CURRENCY', 'zł')
('NLS_COMP', 'BINARY')
('NLS_LENGTH_SEMANTICS', 'BYTE')
('NLS_NCHAR_CONV_EXCP', 'FALSE')And values of NLS are proper also from GDB level:
Breakpoint 1, 0x000000000e4e4840 in lxhlmod ()
(gdb) set $vec = $x0
(gdb) x/1bs $vec+0xa0
0xffffa1b5b688: "zł"I checked SQL*Plus on different machine – Oracle 19.30 on X86_64 Linux:
(gdb) b lxhlmod
Breakpoint 1 at 0x47530f0
(gdb) c
Continuing.
Breakpoint 1, 0x00000000047530f0 in lxhlmod ()
(gdb) set $vec = $rdi
(gdb) x/1bs $vec+0xa0
0x7fd539fef688: "zl"The same thing! Before ALTER we see "zl", and after ALTER:
Breakpoint 1, 0x00000000047530f0 in lxhlmod ()
(gdb) x/1bs $vec+0xa0
0x7fd539fef688: "zł"So the problem is somewhere in SQL*Plus or driver… is it possible to simulate correct behavior in SQL*Plus and incorrect behavior in different clients?
Let’s try SQL*Plus with NLS_LANG env set:
[oracle@vrick19 ~]$ export NLS_LANG=POLISH_POLAND.AL32UTF8
[oracle@vrick19 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Pn Lip 13 16:34:51 2026
Version 19.25.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Połączono z:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
SQL> @spid
SPID
------------------------
7431
SQL> alter session set nls_territory=poland;And from GDB:
(gdb) b lxhlmod
Breakpoint 1 at 0xe4e4840
(gdb) c
Continuing.
Breakpoint 1, 0x000000000e4e4840 in lxhlmod ()
(gdb) set $vec=$x0
(gdb) x/1s $vec+0xa0
0xffff964dd688: "zł"When NLS_LANG is absent, this SQL*Plus 19c client does not infer the client character set from the database character set or from the UTF-8 terminal locale. Its NLS initialization code falls back to the legacy US7ASCII client character set.
We can see it here:
SQL> select distinct client_charset
from v$session_connect_info
where sid = sys_context('USERENV', 'SID'); 2 3
CLIENT_CHARSET
----------------------------------------
US7ASCIIBingo!
So where the client chatacter set is chosen?
I found something like this:
libsqlplus.so:afiini
→ libclntshcore.so.19.1:lxhLangEnv
→ lxhenvquery
→ slzgetevar("NLS_LANG")
→ lxhLaToId
→ lxpcget
→ lxpcsetLet’s start sqlplus with gdb and set breakpoints:
[oracle@vrick19 ~]$ gdb --args "$ORACLE_HOME/bin/sqlplus" "/ as sysdba"
(gdb) set breakpoint pending on
(gdb) break lxpcget
Function "lxpcget" not defined.
Breakpoint 1 (lxpcget) pending.
(gdb) run
Starting program: /u01/app/oracle/product/19.19/dbhome_1/bin/sqlplus
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
Breakpoint 1, 0x0000fffff4eb67b8 in lxpcget () from /u01/app/oracle/product/19.19/dbhome_1/lib/libclntshcore.so.19.1
Missing separate debuginfos, use: yum debuginfo-install glibc-2.28-251.0.3.el8_10.27.aarch64 libaio-0.3.112-1.el8.aarch64 libgcc-8.5.0-28.0.1.el8_10.aarch64
(gdb) bt
#0 0x0000fffff4eb67b8 in lxpcget () from /u01/app/oracle/product/19.19/dbhome_1/lib/libclntshcore.so.19.1
#1 0x0000fffff4eab4c0 in lxhLaToId () from /u01/app/oracle/product/19.19/dbhome_1/lib/libclntshcore.so.19.1
#2 0x0000fffff4ea6b8c in lxhenvquery () from /u01/app/oracle/product/19.19/dbhome_1/lib/libclntshcore.so.19.1
#3 0x0000fffff4eab890 in lxhLangEnv () from /u01/app/oracle/product/19.19/dbhome_1/lib/libclntshcore.so.19.1
#4 0x0000fffff7f434fc in afiini () from /u01/app/oracle/product/19.19/dbhome_1/lib/libsqlplus.so
#5 0x0000fffff7f3b34c in afidrv () from /u01/app/oracle/product/19.19/dbhome_1/lib/libsqlplus.so
#6 0x0000fffff45ea2ac in __libc_start_main () from /lib64/libc.so.6
#7 0x0000000000400810 in _start ()Now I can check character set value:
(gdb) set $tbl = *(char **)$x2
(gdb) set $rec = $tbl + ($w3 * 0x28) + 0x30
(gdb) x/s $rec + 9
0x43f2e1: "US7ASCII"Different clients will set their character sets in different ways of course and it can lead to many different behaviors.
Be aware how you application server is setting client character sets, because they affect not only visuals, but can have important impact on performance!
Check your APEX applications!
SQL> ;
1* select OSUSER, CLIENT_CHARSET from v$session_connect_info
SQL> /
OSUSER CLIENT_CHARSET
------------------------------ ----------------------------------------
oracle US7ASCII
oracle US7ASCII
oracle US7ASCII
oracle US7ASCII
tomcat Unknown
tomcat Unknown
tomcat Unknown
tomcat Unknown
tomcat Unknown
tomcat Unknown
tomcat Unknown
OSUSER CLIENT_CHARSET
------------------------------ ----------------------------------------
tomcat Unknown
tomcat Unknown
oracle Unknown
oracle Unknown
oracle Unknown
Wybrano wierszy: 16.CLIENT_CHARSET=Unknown is not by itself evidence of an incorrect APEX or JDBC character-set configuration. For such clients, verify Unicode handling with an explicit round-trip test and inspect the JDBC/ORDS configuration.
The child cursor split is real: the previous NLS handle contains zl, while the handle rebuilt by ALTER SESSION SET NLS_TERRITORY=POLAND contains zł. kksIsNLSEqual therefore correctly reports an NLS mismatch.
The misleading zl -> zl output is a separate client-side effect. With NLS_LANG unset, this SQL*Plus 19c client falls back to US7ASCII. When the diagnostic XML and NLS_SESSION_PARAMETERS results are converted for that client, ł cannot be represented and is displayed as l.